VBA macro to send Emails from Word: First line font

BigMac'ro · #1 02-26-2025, 12:49 AM

Hello,

I'm working on a macro allowing me to send Emails (via Outlook) to several persons. It is based on an Excel-List containing the Email-addresses and on a Word document as the body of the mail. It works quite well, and I just found a way to include the default signature after the mail body

During testing, I was surprised that the first line of the Email is always formatted in a different font. The first line is Times New Roman, size 12; the rest of the mail is in Calibri, size 10. In the source document in Word, everything is Arial, size 10.

Does anyone know where this comes from and how it can be solved?

Here is the code relevant for sending the mails:

Code:

Set objMail = objOutlook.CreateItem(0)
      Dim signature As String                 


' E-Mail settings     
With objMail
         .Display
         signature = objMail.HTMLBody
         .To = strTo
         .Subject = strSubj
         .BodyFormat = 2
         .HTMLBody = strBody & signature
         For a = LBound(strIndivAttach) To UBound(strIndivAttach)          ' in case there are several attachments
             If Dir(strIndivAttach(a)) <> "" Then
                 .Attachments.Add (strIndivAttach(a))
             End If
         Next a
         .Send   ' Send
     End With

(you will have guessed: strIndivAttach is an array of strings containing the paths to the attachments - but I don't think that's relevant here)

It's also worth noticing that this issue does not occur if I send the Emails without signature - i.e. when I remove

Code:

.Display
signature = objMail.HTMLBody

and have only

Code:

.HTMLBody = strBody

in the code above.

Thank you!

gmayor · #2 02-26-2025, 02:19 AM

You could use the Outlook word editor to do whatever you wish with the message body e.g. in the following example wdDoc is the message body, and oRng is the range in question. By collapsing the range to the start, you can retain the default signature associated with the account. If you want to lose the signature, don't collapse the range.
If you want to keep all the formatting in the original document, copy the document range and paste it to oRng instead of formatting a text string.

Or you could simply use E-Mail Merge Add-in

Code:

Dim olInsp As Object
Dim objMail As Object
Dim wdDoc As Object
Dim oRng As Object

Set objMail = objOutlook.CreateItem(0)


' E-Mail settings
    With objMail
        .To = strTo
        .Subject = strSubj
        .BodyFormat = 2
        
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        oRng.Collapse 1
        .Display
         oRng.Text = strBody
         oRng.Font.Name = "Arial"
         oRng.Font.Size = 10
         For a = LBound(strIndivAttach) To UBound(strIndivAttach)          ' in case there are several attachments
             If Dir(strIndivAttach(a)) <> "" Then
                 .Attachments.Add (strIndivAttach(a))
             End If
         Next a
         .send   ' Send
     End With

BigMac'ro · #3 02-26-2025, 05:55 AM

Thank you for your answer, gmayor.

The E-Mail Merge Add-in (which, if understood well after a glimpse on the website, you developped yourself - congratulations!) might help me out in theory, however I cannot install software or add-ins by myself. And I doubt that the IT department of my company will be open for it...

If I do as suggested in your code, I have a uniform formating + the signature, but special formating from the word document is lost (e.g. words in bold or underlined parts). Moreover, between the end of the body and the signature, additional text I would like to get rid of appears:

HTML Code:

  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML>  <HEAD>  <META NAME="Generator" CONTENT="MS Exchange Server version rmj.rmm.rup.rpr">  <TITLE></TITLE>  </HEAD>  <BODY>  <!-- Converted from text/plain format -->     </BODY>  </HTML>

</div> I guess that's somehow related to the fact I use HTML as body format?

You say

Quote:

If you want to keep all the formatting in the original document, copy the document range and paste it to oRng instead of formatting a text string.

That's actually what I want, but I don't understand what you mean by "copy the document range and paste it to oRng instead of formatting a text string". Could you clarify?

Thank you!

gmayor · #4 02-26-2025, 07:46 AM

Not having seen the rest of your code, it is difficult to be specific, but let us assume that you have named the document e.g.

Code:

Dim oSource as document
Set oSource = ActiveDocument.

Then copy the oSource.range to the clipboard

Code:

oSource.Range.Copy

and replace

Code:

oRng.Text = strBody
oRng.Font.Name = "Arial"
oRng.Font.Size = 10

with

Code:

oRng.Paste

to put the formatted document (minus header/footers which are not supported in mail messages) as the message body.

BigMac'ro · #5 02-26-2025, 08:13 AM

Thank you! I actually just found a solution, it appears it's quite similar to yours

:

Code:

doc.Content.Copy


With objMail
        .Display
        .To = strTo
        .CC = strCC
        .BCC = strBCC
        .Subject = strSubj
        .BodyFormat = olFormatHTML
        
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range(0, wdDoc.Characters.Count)
        If Not (wdDoc Is Nothing) Then
            wdDoc.Content.Paste
        End If
        
        For a = LBound(strIndivAttach) To UBound(strIndivAttach) ' several attachements
            If Dir(strIndivAttach(a)) <> "" Then
                .Attachments.Add (strIndivAttach(a))
            End If
        Next a
        '.Send 
    End With

I used .Content.Copy, you use .Range.Copy. I'll also try your way to see if I can add the default Outlook signature. I did not find a way to do that yet, except pasting the signature in the Word document beforehand (not very elegant).

gmayor · #6 02-26-2025, 11:03 PM

If you collapse the range to its start before pasting, the default signature, which is at the end of the message range, is preserved.

Code:

Set oRng = wdDoc.Range
oRng.Collapse 1
oRng.Paste

BigMac'ro · #7 02-26-2025, 11:49 PM

Thanks a lot, it works like a charm now!

Last thing I have to figure out now is how to replace some keywords in the mail automatically. The sending process is actually part of a loop as there are several receivers. When using a string for the body of the mail, I could replace this quite easily before sending:

Code:

strBody = Replace(strBody, "%Keyword%", replacement)

I'll have to find another solution here and will try out a few things today. In my first attempt

Code:

doc.Content.Text = Replace(doc.Content.Text, "%Keyword%", replacement)

the replacement worked, but all formating got lost (also for the signature)

Replacements have to be undone after sending each mail, so keywords can still be found for the next one.

gmayor · #8 02-27-2025, 10:56 PM

Why don't you replace the words in the document before adding it to the message body? Based on your comments, you could insert content controls in the document and write the required values to them before adding it to the messages.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can send emails, cannot receive emails!	jpcummins	Outlook	0	09-02-2020 02:39 PM
Bigger font on first letter of first line leaves large gap on that line	CrossReach	Word	1	04-12-2016 09:23 AM
Send Emails from MailMerge in Word using Specified Email Address	Baldeagle	Mail Merge	6	07-25-2015 05:32 AM
macro to add brackets to each line and add single quotes to each word in the line	bracketandquotes	Word VBA	17	02-16-2015 03:51 PM
How to change line height for marked text (in Word 2007)? ... as default for font?	pstein	Word	1	01-14-2012 10:15 AM