I have another macro throwing me an error. I would like to do the following: Open Outlook template, change subject, add attachment and add a specific range into the email body. 1 to 3 seem to work but code will baulk at the last bit. - When I step through the code, the code chokes on ".HTMLBody = Replace(OutMail.HTMLBody, strPlaceHolder, strTable)" and it give me a TypeMismatch error.
My VBA is really very pedestrian so I'm quite stuck. I have tried to look the error up but since the items aren't declared, it doesn't seem to apply?
I have reviewed Ron de Bruin's site but it seems to me what he does is have only the Excel Range in the HTML email body. (And apart from the fact that I'm loath to use code I don't understand at all!). I only want to add the range to the pre-written email. It will need to retain the formatting as well but that's an issue I don't even want to think about yet. When I do it manually, it's a simply copy and paste job. How hard can it be?
The code also doesn't execute the date formatting correctly. It's displayed 28/04/2017. At a pinch that will work, though I would prefer 28 April 2017.
Public Sub SendHarddownReport()
Dim OutApp As Object
Dim OutMail As Object
Dim strReportSheet As String
Dim dtToday As Date
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\305015724\Documents\Templates\ANZ_Harddown_Report.oft")
On Error GoTo Errorcatch
'Your Sheet names need to be correct in here
Set sh1 = Sheets("Harddown ANZ")
Set sh2 = Sheets("Table")
strReportSheet = "C:\Users\305015724\Documents\Service\ANZ_HarddownReport.xlsm"
dtToday = Format(Now, "d mmmm YYYY")
On Error GoTo cleanup
strPlaceHolder = "Attach report to email and Change date in subject"
strTable = sh2.Range("A16:G40")
.Subject = "ANZ Harddown Report - " & dtToday
.HTMLBody = Replace(OutMail.HTMLBody, strPlaceHolder, strTable)
.Display 'or use .Send
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
Any pointers will be greatly appreciated.