We help IT Professionals succeed at work.

Email from Excel 2007 / 2010

NormanMitchell
on
Medium Priority
321 Views
Last Modified: 2012-08-16
I need some VBA code to format the body of an email where the text is created within Excel and then sends the e-mail via Outlook. All of my  VBA to achieve thsi is in Excel

The basic process works well but the e-mail needs to have specific formatting for the text.

Any help will be very much appreciated.
Comment
Watch Question

SteveCost Accountant
CERTIFIED EXPERT
Top Expert 2012

Commented:
Could you post your existing code as there are many ways to Use Excel & Outlook together.

Author

Commented:
Here is an extract from the code:

'Set parameters
    Set objExcel = Application
    Set wksWorksheet = objExcel.ActiveSheet
    Set wkbWorkbook = objExcel.ActiveWorkbook

    'Message
    strEMailAddress = ActiveSheet.Cells(18, 4)

   
 'Build the body of the e-mail
    strPara1 = "Text for paragraph 1"
    strPara2 = "Text for paragraph 2"
    strPara3 = "Text for paragraph 3"
    strPara4 = "Text for paragraph 4"
    strSalutation = "Best regards" & vbcr & vbcr & "Norman Mitchell"
     
    'E-mail text
    If strPara1 <> "" Then
        strBody = strPara1
    End If
   
    If strPara2 <> "" Then
        strBody = strBody & vbCr & vbCr & strPara2
    End If
   
    If strPara3 <> "" Then
        strBody = strBody & vbCr & vbCr & strPara3
    End If
   
    If strPara4 <> "" Then
        strBody = strBody & vbCr & vbCr & strPara4
    End If
   
    'Salutation
    strBody = strBody & vbCr & vbCr & strSalutation _
                & vbCr & vbCr & Application.UserName
   

'Send e-mail
    Set olApp = CreateObject("Outlook.Application")
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olFolder = olNameSpace.GetDefaultFolder(6)
    Set olMail = olApp.CreateItem(0)
   
    With olMail
        .Subject = strSubject
        .Recipients.Add strEMailAddress
        .Body = strBody

        If booSave = True Then
            .Save
        Else
            .Send
        End If
    End With
   
'Remove objects
    Set objExcel = Nothing
    Set wksWorksheet = Nothing
    Set wkbWorkbook = Nothing

    Set olApp = Nothing
    Set olNameSpace = Nothing
    Set olFolder = Nothing
    Set olMail = Nothing
Cost Accountant
CERTIFIED EXPERT
Top Expert 2012
Commented:
in place of using .Body = strBody have you tried using :

.HTMLBody = strBody
.BodyFormat = 3 '1=Plain text 2=HTML 3=RichText 

Open in new window

Author

Commented:
Not the complete answer but it pointed me in the right direction. Many thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.