VBA code to add company logo at the end of the email with attachements

A

AccountantHarry

New Member
Joined
Oct 13, 2019
Messages
18
Hi

I have a macro which I wrote from watching a couple of YouTube videos, it works however, I would like to add the company logo at the bottom of the email, the image is saved in W:\company\logo.jpg. My codes are as follows:

Sub send_email_with_attachments()


On Error Resume Next
Dim o As Outlook.Application
Set o = New Outlook.Application
Dim omail As Outlook.MailItem

Dim i As Long
For i = 13 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

Set omail = o.CreateItem(olMailItem)

With omail

.To = Cells(i, 1).Value
.CC = Cells(i, 2).Value
.Subject = Cells(i, 3).Value
.Body = "Hi " & Cells(i, 4).Value & vbNewLine & vbNewLine & Cells(i, 5).Value & vbNewLine & vbNewLine & Cells(i, 6).Value & "Kind regards" & vbNewLine & "Harriet"
.Attachments.Add Cells(i, 7).Value
.Attachments.Add Cells(i, 8).Value
.Attachments.Add Cells(i, 9).Value


.Display


End With
Next

End Sub



Please help.
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Try this:

Adjust the image size in numbers in red.
Code: 
Sub send_email_with_attachments()
  On Error Resume Next
  Dim o As Outlook.Application, omail As Outlook.MailItem, i As Long
  Dim sBody As String
  Set o = New Outlook.Application
  For i = 13 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    Set omail = o.CreateItem(olMailItem)
    With omail
      .To = Cells(i, 1).Value
      .CC = Cells(i, 2).Value
      .Subject = Cells(i, 3).Value
      sBody = "Hi " & Cells(i, 4).Value & "<br><br>" & _
                      Cells(i, 5).Value & "<br><br>" & _
                      Cells(i, 6).Value & "Kind regards" & "<br><br>"
      .Attachments.Add "W:\company\logo.jpg"
      .Attachments.Add Cells(i, 7).Value
      .Attachments.Add Cells(i, 8).Value
      .Attachments.Add Cells(i, 9).Value
      .HTMLBody = "<html>" & "******>" & sBody & _
                    "<img src=cid:logo.jpg height=[COLOR=#ff0000]150 [/COLOR]width=[COLOR=#ff0000]150[/COLOR]>" & _
                    "</body>" & "</html>"
      .Display
    End With
  Next
End Sub
 
Last edited:
Upvote 0
Hi

It only shows ******>img src=cid:logo.jpg height=150 width=150>
and the attachments, the body of the email has disappear totally.

Regards







DanteAmor said:
Try this:

Adjust the image size in numbers in red.
Code: 
Sub send_email_with_attachments()
  On Error Resume Next
  Dim o As Outlook.Application, omail As Outlook.MailItem, i As Long
  Dim sBody As String
  Set o = New Outlook.Application
  For i = 13 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    Set omail = o.CreateItem(olMailItem)
    With omail
      .To = Cells(i, 1).Value
      .CC = Cells(i, 2).Value
      .Subject = Cells(i, 3).Value
      sBody = "Hi " & Cells(i, 4).Value & "

" & _
                      Cells(i, 5).Value & "

" & _
                      Cells(i, 6).Value & "Kind regards" & "

"
      .Attachments.Add "W:\company\logo.jpg"
      .Attachments.Add Cells(i, 7).Value
      .Attachments.Add Cells(i, 8).Value
      .Attachments.Add Cells(i, 9).Value
      .HTMLBody = "" & "******>" & sBody & _
                    "[IMG]https://www.mrexcel.com/forum/logo.jpg[/IMG]150 width=[COLOR=#ff0000]150[/COLOR]>" & _
                    "" & ""
      .Display
    End With
  Next
End Sub
Click to expand...
 
Upvote 0
The forum editor modified my code. :mad:

Try the following:

Change these lines:

Code: 
      .HTMLBody = "<html>" & "******>" & sBody & _
                    "<img src=cid:logo.jpg height=150 width=150>" & _
                    "</body>" & "</html>"


For these:

Code: 
.HTMLBody = "< html >" & "< body >" & sBody & _
                    "< img src=cid:logo.jpg height=150 width=150 >" & _
                    "< /body >" & "< /html >"

After and before each symbol <I had to put a space, otherwise the editor makes changes. Please remove those spaces.
It should look like this:

71c998fb28523a3f0e597b91c015b08f.jpg
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA is centering the image instead of aligning it to the left as usual in New Email
Replies
1
Views
140
Micron
M
O
  • Question Question
Add Signature to Email Macro
Replies
1
Views
331
starl
starl
S
  • Solved
Tweak to VBA email code
Replies
3
Views
757
rollis13
rollis13
H
  • Question Question
Macro to email Report
Replies
2
Views
549
howard
H
The Ruff Report
  • Question Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
885
The Ruff Report
The Ruff Report

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top