?
Solved

Email from Excel 2007 / 2010

Posted on 2012-08-15
4
Medium Priority
?
305 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.
0
Comment
Question by:NormanMitchell
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38295452
Could you post your existing code as there are many ways to Use Excel & Outlook together.
0
 

Author Comment

by:NormanMitchell
ID: 38295560
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
0
 
LVL 24

Accepted Solution

by:
Steve earned 1500 total points
ID: 38295645
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

0
 

Author Closing Comment

by:NormanMitchell
ID: 38300783
Not the complete answer but it pointed me in the right direction. Many thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article lists the top 5 trialware OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their Exchange server is no longer available or other critical issues with Exchange server or impo…
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

809 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