jsabo
02-28-2013, 12:31 PM
I need to run a macro from a workbook which will automatically create an email with the workbook attached. Can someone please take the code as i have it below and change it to support HTML email format? I need to be able to format auto-populated body to include <b> for bold, etc... please help.
'begin email creation
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, MailTxt As String
'************************************************* ********
'Set email details; Comment out if not required
Const MailTo = ""
Const MailCC = ""
Const MailBCC = ""
MailSub = "Code 2 and 3 Documents - Please Respond"
MailTxt = "Please see the attached which shows which documents are currently Code 2 or 3. The shaded items in column J are crucial as they have been with you for over 10 days. These need to be revised based on the comments you've received and returned ASAP. Thank you!"
'************************************************* ********
'Turns off screen updating
Application.ScreenUpdating = False
'Makes a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Code 2 and 3.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName
'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = MailTo
.CC = MailCC
.BCC = MailBCC
.importance = 2
.FlagDueBy = DateAdd("d", 1, Now)
.Subject = MailSub
.Body = MailTxt
.Attachments.Add WB.FullName
.Display
End With
'Deletes the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
'Restores screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
'end email creation
'begin email creation
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, MailTxt As String
'************************************************* ********
'Set email details; Comment out if not required
Const MailTo = ""
Const MailCC = ""
Const MailBCC = ""
MailSub = "Code 2 and 3 Documents - Please Respond"
MailTxt = "Please see the attached which shows which documents are currently Code 2 or 3. The shaded items in column J are crucial as they have been with you for over 10 days. These need to be revised based on the comments you've received and returned ASAP. Thank you!"
'************************************************* ********
'Turns off screen updating
Application.ScreenUpdating = False
'Makes a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Code 2 and 3.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName
'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = MailTo
.CC = MailCC
.BCC = MailBCC
.importance = 2
.FlagDueBy = DateAdd("d", 1, Now)
.Subject = MailSub
.Body = MailTxt
.Attachments.Add WB.FullName
.Display
End With
'Deletes the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
'Restores screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
'end email creation