When I normally use Outlook and click New Mail Message, I get a formatted blank email which has a signature, default font, business card etc.
My VBA below successfully creates an Outlook email from Excel. The email it creates is unformatted and doesn't have the signature etc as described above.
My guess is it's something to do with the way the VBA creates the mail using the CreateObject command, or something.
So my question is, how do I alter the code so that the VBA creating an email mimics the New Mail Message button in Outlook? Then I can have nice looking emails.
I hope that's clear.
Dim ws As Worksheet, sTo As String, sAmount As String, sDueDate As String, sSubject As String
Dim oOutlookApp As Object, oOutlookMail As Object
Set oOutlookApp = CreateObject("Outlook.Application")
Application.ScreenUpdating = False
Set ws = ActiveSheet
sTo = ActiveCell.Offset(52, 0).Value
sAmount = ActiveCell.Offset(11, 0).Value
sDueDate = ActiveCell.Value
sSubject = ActiveCell.Offset(-2, 0).Value
sContact = ActiveCell.Offset(51, 0).Value
sHosting = ActiveCell.Offset(53, 0).Value
Set oOutlookMail = oOutlookApp.CreateItem(0)
On Error Resume Next
.To = sTo
.CC = ""
.BCC = ""
.Subject = "Annual renewal of website www." & sSubject
.Body = "Dear " & sContact & vbCrLf & vbCrLf & "Your website www." & sSubject & " annual " & sHosting & " is due for renewal on " & sDueDate & "." & vbCrLf & vbCrLf & "Please find attached an invoice for $" & sAmount & " for one years renewal of these services." & vbCrLf & vbCrLf & "This email is automatically generated. Please feel free to contact us should you need help. If you consider this invoice is incorrect, or you have been wrongly sent this mail, please contact us." & vbCrLf & vbCrLf & "Croxford Technology" & vbCrLf & "68b Kennels Lane" & vbCrLf & "RD2, Wanaka" & vbCrLf & "New Zealand" & vbCrLf & "Ph +64 (0)3 443 4672" & vbCrLf & "Email: email@example.com" & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Will Croxford"
On Error GoTo 0
Set oOutlookMail = Nothing
Set oOutlookApp = Nothing
Application.ScreenUpdating = True