Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA create Outlook Email format

Posted on 2010-08-23
6
Medium Priority
?
1,070 Views
Last Modified: 2012-05-10
Hi Experts

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.

Regards,

Will
Sub SendOutlookInvoice()
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")
oOutlookApp.Session.Logon
    
Cells(5, ActiveCell.Column).Select

Application.ScreenUpdating = False
    
Set ws = ActiveSheet
    
With ws
    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
End With
    
Set oOutlookMail = oOutlookApp.CreateItem(0)
On Error Resume Next
With oOutlookMail
    .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: will@croxford.co.nz" & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Will Croxford"
    .Display
End With
On Error GoTo 0
    
Set oOutlookMail = Nothing
Set oOutlookApp = Nothing
    
Application.ScreenUpdating = True
    
End Sub

Open in new window

0
Comment
Question by:willnjen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
Gary Dewrell earned 1500 total points
ID: 33505697
Not a VBA expert but see if this at least gets you pointed in the right direction.

http://www.rondebruin.nl/mail/folder3/signature.htm
0
 

Author Closing Comment

by:willnjen
ID: 33506191
Thanks, I had already glanced over that website and didn't understand it, but after you pointed me there again I re-read it and figured it out.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33506224
Hi, Will.

The problem is line 30.  It's wiping the signature and anything else in the message body out in favor of the content line 30 inserts.  Change the line to

    .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 begin_of_the_skype_highlighting              +64 (0)3 443 4672      end_of_the_skype_highlighting" & vbCrLf & "Email: will@croxford.co.nz" & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Will Croxford" & .Body

This will insert the contents from line 30 in front of the signature and/or anything else that's already in the body.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:willnjen
ID: 33506281
FYI, I've merged rondebruin's way with what I had, to come up with this (still needs tidying)

Sub SendOutlookInvoice2()
Dim ws As Worksheet, sTo As String, sAmount As String, sDueDate As String, sSubject As String
Dim oOutlookApp As Object, oOutlookMail As Object
Dim SigString As String
Dim Signature As String
Dim strbody As String

Set oOutlookApp = CreateObject("Outlook.Application")
oOutlookApp.Session.Logon
    


Cells(5, ActiveCell.Column).Select

Application.ScreenUpdating = False
    
Set ws = ActiveSheet
    
With ws
    sTo = ActiveCell.Offset(52, 0).Value
    sAmount = ActiveCell.Offset(14, 0).Value
    sDueDate = ActiveCell.Value
    sSubject = ActiveCell.Offset(-2, 0).Value
    sContact = ActiveCell.Offset(51, 0).Value
    sHosting = ActiveCell.Offset(53, 0).Value
End With

strbody = "<SPAN>Dear </SPAN>" & sContact & "<br /><br />" & _
              "Your website www." & sSubject & " annual " & sHosting & " is due for renewal on " & sDueDate & "." & "<br /><br />" & _
              "Please find attached an invoice for " & sAmount & "0 +GST for one years renewal of these services." & "<br /><br />" & _
              "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." & "<br /><br />" & _
              "<br><br>Regards" & _
              "<br><br>Will Croxford"
              
Set oOutlookMail = oOutlookApp.CreateItem(0)

    SigString = "C:\Users\Will\AppData\Roaming\Microsoft\Signatures\Croxford Technology VCard.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If


On Error Resume Next
With oOutlookMail
    .To = sTo
    .CC = ""
    .BCC = ""
    .Subject = "Annual renewal of website www." & sSubject
    .HTMLBody = strbody & "<br><br>" & Signature
    '"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: will@croxford.co.nz" & vbCrLf & vbCrLf & "Regards," & vbCrLf & vbCrLf & "Will Croxford" & vbCrLf & vbCrLf & Signature
    .Display
End With
On Error GoTo 0
    
Set oOutlookMail = Nothing
Set oOutlookApp = Nothing
    
Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 

Author Comment

by:willnjen
ID: 33506465
Hi BlueDevil

You are correct and your simple solution would have saved me time, but I've already done the other HTML method to resolve my problem.  

Thanks for your suggestion anyway.

Will
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33506841
You're welcome, Will.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question