PDA

View Full Version : Generate HTML email with file attached - please help



jsabo
03-11-2013, 07:37 AM
Please see the code below. I am trying to run this at the end of a script to generate an email with the Excel file attached. Right now, it is saving the file as a csv file. Can you please show me how to generate the email with a highest compatibility multi-sheet workbook attached instead? Thanks!

'begin email

Dim OutApp As Object, _
OutMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, strbody As String

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

Set WB = ActiveWorkbook
FileName = "Snapshot " & Format(Now, "dd-mmm-yy")
On Error Resume Next
On Error GoTo 0
WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName


strbody = "<H3><B>Hello, </B></H3>" & _
"Please see the attached which offers a current snapshot of where your performance is for your subcontract with the company.<br>" & _
"Please see the stats below:<br><br>" & _
"<br><br><B>Thank you</B>"


On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.importance = 2
.FlagDueBy = DateAdd("d", 1, Now)
.Subject = "This is the Subject line"
.HTMLBody = strbody
.Attachments.Add WB.FullName
.Display
End With

WB.Close SaveChanges:=False

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

enrand22
03-11-2013, 10:09 AM
modify this line:
WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName & ".xlsx", fileformat:=50

jsabo
03-11-2013, 10:28 AM
tried that, it gives me a run time error that states the following:

"Run-time error '1004':

This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type"

The problem may be that our database spits out CSV files and thats what im starting with when i run the macro...

enrand22
03-11-2013, 10:37 AM
and if you use fileformat:=51 instead of fileformat:=50 wich of course is if you are using excel 2007 +

jsabo
03-11-2013, 11:02 AM
ah that did it. thanks so much!