CODEWITHSUNDEEP
htmlvbaexcel
Dan Donoghue
Dan Donoghue

Reputation: 6196

Setting a background image using HTML in an outlook email using Excel VBA

I am trying to use Excel VBA to create an email regarding the Childrens Cancer Institute of Australia with a background image (CCIALittleGirl.jpg).

I then want to have text or a text box ideally over it with white bold text that I can populate at runtime.

I can add an image to the email (the commented out MyHTML part does that) but I can't seem to get a background image to load in, I am modifying code I found online somewhere but my HTML skills are pretty close to nill.

I am pretty sure this part is my issue:

<div style=""background-image: ""cid:CCIALittleGirl.jpg""

Here is the code I have so far, the MyHTML part doesn't do what I expect.

Private Sub EmailCopy()
Dim oApp, oMail As Object, MyHTML As String
    Application.ScreenUpdating = False
    On Error Resume Next
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    'MyHTML = "<p>TempText</p>"
    'MyHTML = MyHTML & "<img src=""cid:CCIALogo.jpg"">"
    MyHTML = "<div style=""background-image: ""cid:CCIALittleGirl.jpg&quot""; height: 390px; width: 900px; color: rgb(0, 0, 0); margin-top: 0px; padding-left: 35px; padding-top: 25px;"">"
    MyHTML = MyHTML & "my text appears here and on top of the image"
    MyHTML = MyHTML & "</div>"
    With oMail
        .To = "[email protected]"
        .Subject = "TEST"
        .Attachments.Add "C:\Images\CCIA\CCIALogo.jpg"
        .Attachments.Add "C:\Images\CCIA\CCIALittleGirl.jpg"
        .HTMLBody = MyHTML
        .Display
        .Save
        .Close False
    End With
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Thanks for any help you can offer.

Upvotes: 0

Views: 3869

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6196

Worked it out:

MyHTML = "<body background=""cid:CCIALittleGirl.jpg"""

This successfully puts the image in as a background.

For completeness (in case anyone else has the same question) here is the complete solution:

Private Sub EmailCopy()
Dim oApp, oMail As Object, MyHTML As String, WB As Workbook, FileName As String, BodyText As String, MyText As String
    Application.ScreenUpdating = False
    FileName = ArchiveFolder & ArchiveFileName
    On Error Resume Next
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    MyText = "Please find attached the CCIA report for " & Format(Now, "DD/MM/YYYY") & "<br><br><br><br><br><br><br><br><br><br>" & _
    "Congratulations to " & StrConv(Sheets("Summary").Range("A2").Text, vbProperCase) & "<br>" & _
    "For an amount of: " & Replace(Sheets("Summary").Range("C2").Text, " ", "") & "<br>" & _
    "Across " & Trim(Sheets("Summary").Range("B2").Text) & " donations."
    MyHTML = "<body background=""cid:CCIALittleGirl.jpg""; center top no-repeat;"
    MyHTML = MyHTML & vbCrLf & "<p style=""font-size:30px;font-weight:Bold;color:rgb(100%,100%,100%)"">" & MyText & "</p>"
    MyHTML = MyHTML & vbCrLf & "<br><br><br><br><img src=""cid:CCIALogo.jpg"">"
    With oMail
        .to = "[email protected]"
        .Subject = "CCIA report @ " & Format(Now, "DD/MM/YYYY")
        .Attachments.Add "C:\Images\CCIA\CCIALogo.jpg"
        .Attachments.Add "C:\Images\CCIA\CCIALittleGirl.jpg"
        .Attachments.Add FileName & ".xlsx"
        .HTMLBody = MyHTML
        .Display
        .Save
        .Close False
    End With
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Upvotes: 1

AndrewL64
AndrewL64

Reputation: 16301

Replace <div style=""background-image: ""cid:CCIALittleGirl.jpg""></div> with this <div style="background-image: cid:CCIALittleGirl.jpg"></div>

Upvotes: 0

Related Questions