Format email body-Using Excel VBA to send mail

Claytocb · #1 01-08-2013, 04:41 PM

Hello

I am relatively new to VBA and just trying to learn some things to help automate some processes. I have the below VBA code attached to a command button to send an email from excel with an attachment.
The code works great but is there a way to format the text in the email body and/or include any type of logo or signature in the body?

Thanks!

Code:

Private Sub Button1_Click() 
    sendemail 
End Sub 
 
Public Function sendemail() 
    esubject = "Daily Report" 
    sendto = "brad2343@gmail.com" 
    ebody = Range("A7") & vbCrLf & Range("A8") & vbCrLf & Range("A9") & vbCrLf & Range("A10") 
    newfilename = "C:\user\docs\Letter.doc" 
     
    Set app = CreateObject("Outlook.Application") 
    Set itm = app.createitem(0) 
     
    With itm 
         
        .Subject = esubject 
        .to = sendto 
        .body = ebody 
        .attachments.Add (newfilename) 
        .Display 
        .send 
    End With 
     
    Set app = Nothing 
    Set itm = Nothing 
     
End Function

macropod · #2 01-31-2013, 11:58 PM

Hi Claytocb,

To have a formatted body, you'd need to set the BodyFormat property to Rich Text or HTML. See: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx. As shown in the link, for HTML you can simply add the relevant HTML tags to your code. For RTF, however, the Outlook Object Mode doesn't actually provide any support for writing the formatted message body. For that, you'd need to use a 3rd-party API, such as Outlook Redemption, which provides the RTFBody property, thus make it possible to write rtf values in to your message body. See: http://www.dimastr.com/redemption/.

An alternative approach is to use Word as your email editor, which gives you access to its formatting abilities - and the ability to do such things as copying from Excel and pasting via Paste Special in Word.

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Outlook not sending body of email	sbertram	Outlook	0	08-30-2012 11:50 AM
attach multiple excel files to same email using send to option	cbtac	Excel	0	01-04-2012 07:20 AM
Mail merge will not format date field generated by Excel IF statement	borntorun75	Mail Merge	3	12-16-2011 06:28 AM
cant send email directly from WORD (or excel)	etodem	Word	0	04-03-2009 04:59 PM
HTML Format Body - Scripts?	jeffcravener	Outlook	0	11-14-2005 10:58 AM