-
Generate HTML email with file attached - please help
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!
[VBA] '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[/VBA]
-
modify this line:
[VBA]WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName & ".xlsx", fileformat:=50
[/VBA]
-
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...
-
and if you use fileformat:=51 instead of fileformat:=50 wich of course is if you are using excel 2007 +
-
ah that did it. thanks so much!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules