VBA send email OutMail .Body question

[Pinaceous]

Hi All,

I'm using this code that I've come up with on a previous post and it works really very nicely.


https://www.mrexcel.com/forum/excel-questions/1037579-vba-send-email-attachment-question.html


If you take a look at the code, I have the OutMail referencing a cell to gain the information that I need to post inside the email.

So, My question is, 'How do I change the OutMail font?

For example, Can I reference a word document as to be used as template for my OutMail message??

Option Explicit
Sub sendemail()

    Dim FileExtStr As String:confused:
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim varMyAttachment As Variant
  
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        
    With OutMail
        .To = Worksheets("admin").Range("f9").Value
        .CC = Worksheets("admin").Range("f10").Value
        .BCC = ""
        .Subject = Worksheets("admin").Range("i11").Value
        .Body = Worksheets("admin").Range("[B]i[B]12[/B]"[/B]).Value
        'For a single file:
        .Attachments.Add ("C:\File1.xlsb")
        'For multiple files:
        For Each varMyAttachment In Array("C:\File1.xlsb", "C:\File2.xlsb")
            .Attachments.Add varMyAttachment
        Next varMyAttachment
        .Display
    End With
    
End Sub

Please let me know, what are your thoughts hopefully expressed in code.

Thank you!
Pinaceous

 

[Domenic]

I think you'll need to use the HTMLBody property. For example, to set the font to Verdana and the size at 100%, try (note that I've added a space after each occurrence of '<' to prevent the Board from interpreting it as HTML code, so you'll need to remove those spaces)...

.HTMLBody = "< body style=""font-family:verdana; font-size:100%;"">" & [COLOR=#574123]Worksheets("admin").Range("[/COLOR][B]i[B]12[/B]"[/B][COLOR=#574123]).Value[/COLOR] & "< /body>"

If you want to use a template instead, first create one and save the .oft file in a folder. Then replace...

[COLOR=#574123]Set OutMail = OutApp.CreateItem(0)[/COLOR]

with

Set [COLOR=#574123]OutMail[/COLOR] = [COLOR=#574123]OutApp[/COLOR].CreateItemFromTemplate("C:\Users\Domenic\Documents\MyTemplate.oft")

Hope this helps!

 

[Pinaceous]

Hi Domenic,

I'm sorry for the delay, I was still trying to work out the criteria on my end. I've tested out the update that you suggested and it looks really great!

Many thanks again!

Paul