Excel VBA to Send E-mail

Gian624

Board Regular
Joined
Jul 23, 2009
Messages
92
Good Morning,

I’m helping a co-worker modify some code she borrowed from the internet to create an e-mail from an Excel file. Everything works well with one exception. We want to be able to send the e-mail containing the excel file AND the file keep the macro so the file can be reused and sent again. As of now, it tried to use the macro from another existing copy on my desktop when I open it again from the e-mail.

Here is the existing code... cleaned up a little to not show the company sepcific links and modifications:

Sub Mail_ActiveSheet()
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

Sheets("Sheet Info").Copy
Set Destwb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End With

TempFilePath = Environ$("temp") & ""
TempFileName = Sourcewb.Name & " " & Format(Now, "mm-dd-yy")

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

' The E-Mail is generated using the following format and message
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = Range("H4")
.cc = ""
.BCC = ""
.Subject = "Subject Text"
.Body = "Body Text"
.Attachments.Add Destwb.FullName
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Code:
Sub TST()
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String
    
    TempFilePath = Environ$("temp") & "\"
    TempFileName = ActiveSheet.Range("C3") & "_" & ActiveSheet.Range("Q42")
    FileFullPath = TempFilePath & TempFileName
    
    ActiveSheet.ExportAsFixedFormat 0, FileFullPath
    
    On Error Resume Next
    With CreateObject("Outlook.Application").createitem(0)
        .To = "email id"
        .CC = "email id"
        .Subject = "test mail"
        .Body = "testing"
        .Attachments.Add FileFullPath & ".pdf" '--- full path of the pdf where it is saved
        .Send   'or use .Display to show you the email before sending it.
    End With
    On Error GoTo 0
    Kill FileFullPath & ".pdf"
End Sub

change pdf to the format which you want.
File name is determined by cell c3 and q42. change it as per your need
 
Upvote 0
try using "52" instead of PDF.

52 is susbtitute code for xLOpenXML WorkbookMacroEnabled
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top