Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a piece of code that takes a report i've exported, creates a generic e-mail, attaches the report and waits for me to send the e-mail in Outlook.
If i'm producing multiple reports this soon becomes overwhelming so let me ask you - is there any way to save this e-mail rather than displaying it?
Check out my code below:
Sub Emailtest
Recipient = "Test"
CC = "Test_CC"
Company_Name = "CompanyTest"
ReportTitle = "TitleTest"
ExportFilePath = "H:\test.xlsx"
'----------------------------------------------------------
'Create e-mail body in HTML
EmailBody = "<font face = " & Chr(34) & "Calibri" & Chr(34) & ">" &_
"Hi there <BR><BR> Please find attached the " & ReportTitle & " " &_
" as per schedule.<BR><BR> Hope its okay! </FONT>"
'----------------------------------------------------------
'Setup the e-mail
Set myApp = CreateObject ("Outlook.Application")
Set myMessage = myApp.CreateItem(olMailItem)
myMessage.body = ("EmailBody")
myMessage.HTMLBODY = EmailBody
myMessage.BodyFormat = 2 'HTMLFormat
myMessage.To = Recipient
myMessage.CC = CC
myMessage.Subject = Report_Title
myMessage.Attachments.Add(ExportFilePath)
Set myInspector = myMessage.GetInspector 'add signature to e-mail
Set myDoc = myInspector.WordEditor
myMessage.Display
Set myMessage = Nothing
Set myApp = Nothing
Set myInspector = Nothing
Set myDoc = Nothing
End sub
Hi Paul,
Here you go.
Sub Emailtest
Recipient = "Test"
CC = "Test_CC"
Company_Name = "CompanyTest"
ReportTitle = "TitleTest"
ExportFilePath = "C:\Users\Tamilarasu.Nagaraj\Desktop\Philpp\Sheet Wild card.xlsx"
'----------------------------------------------------------
'Create e-mail body in HTML
EmailBody = "<font face = " & Chr(34) & "Calibri" & Chr(34) & ">" &_
"Hi there <BR><BR> Please find attached the " & ReportTitle & " " &_
" as per schedule.<BR><BR> Hope its okay! </FONT>"
'----------------------------------------------------------
'Setup the e-mail
Set myApp = CreateObject ("Outlook.Application")
Set myMessage = myApp.CreateItem(olMailItem)
myMessage.body = ("EmailBody")
myMessage.HTMLBODY = EmailBody
myMessage.BodyFormat = 2 'HTMLFormat
myMessage.To = Recipient
myMessage.CC = CC
myMessage.Subject = ReportTitle
myMessage.Attachments.Add(ExportFilePath)
Set myInspector = myMessage.GetInspector 'add signature to e-mail
Set myDoc = myInspector.WordEditor
myMessage.Display 'Comment this line if you don't want to see outlook draft window
myMessage.Save
myMessage.Close olPromtForSave
Set myMessage = Nothing
Set myApp = Nothing
Set myInspector = Nothing
Set myDoc = Nothing
End sub