Excel: Email VBA Code/Break Link


I currently am using this VBA code below to email a single sheet from my workbook, however when the end user opens the attachment it keeps asking to edit links. What can I add to this code so that it breaks the link upon emailing so they don't get the prompt? Sub Mail_ActiveSheet() ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010 Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook ' Next, copy the sheet to a new workbook. ' You can also use the following line, instead of using the ActiveSheet object, ' if you know the name of the sheet you want to mail : ' Sheets("Sheet5").Copy ActiveSheet.Copy Set Destwb = ActiveWorkbook ' Determine the Excel version, and file extension and format. With Destwb If Val(Application.Version) < 12 Then ' For Excel 2000-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else ' For Excel 2007-2010, exit the subroutine if you answer ' NO in the security dialog that is displayed when you copy ' a sheet from an .xlsm file with macros disabled. If Sourcewb.Name = .Name Then With Application .ScreenUpdating = True .EnableEvents = True End With MsgBox "You answered NO in the security dialog." Exit Sub Else FileExtStr = ".xlsm": FileFormatNum = 52 End If End If End With ' You can use the following statements to change all cells in the ' worksheet to values. ' With Destwb.Sheets(1).UsedRange ' .Cells.Copy ' .Cells.PasteSpecial xlPasteValues ' .Cells(1).Select ' End With ' Application.CutCopyMode = False ' Save the new workbook, mail, and then delete it. TempFilePath = Environ$("temp") & "\" TempFileName = "" Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With Destwb .SaveAs TempFilePath & TempFileName & FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next ' Change the mail address and subject in the macro before ' running the procedure. With OutMail .To = "" .CC = "" .BCC = "" .Subject = "" .Body = "" .Attachments.Add Destwb.FullName ' You can add other files by uncommenting the following statement. '.Attachments.Add ("C:\test.txt") ' In place of the following statement, you can use ".Display" to ' display the mail. .Display End With On Error GoTo 0 .Close SaveChanges:=False End With ' Delete the file after sending. Kill TempFilePath & TempFileName & FileExtStr Set OutMail = Nothing Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub


This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of May 25, 2015.


For more resources for Microsoft Excel