Produce Email and save

[Anonymous]

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

[tamilarasu]

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