PDA

View Full Version : Sending mail with text and image



vsk17
2018-01-02, 07:20 AM
Hi,

I am new to VBA and would require some help. I am trying to send an through excel which has some text in the body of the email and also an image. I am able to add the Image to the email using the outlook application 2016. I am unable to add the text before the image. Could you please help me with the same.

Here is the summary below

Email Body :

Some text
Some text
Some text

Image

Please note that the email text and also image is in the same excel spreadsheet.

Let me know if you need any information.

Thanks in advance
Vinay

Pecoflyer
2018-01-02, 08:26 AM
Hi and welcome

please do not hijack threads but create one of your own.
I did it for you this time - Thanks

vsk17
2018-01-02, 08:43 AM
Sure... Thank you...will do that going forward

Logit
2018-01-02, 04:23 PM
.


Option Explicit




Sub EmailWithOutlook()
Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim FileName As String
Dim wSht As Worksheet
Dim shtName As String
Dim ThisFile As String
Dim oByValue

'ThisFile = "C:\Users\My\Desktop\gun_maintenance.pdf" '<-- This line is required to add a document as attachment to email.
'<-- change path as required

Application.ScreenUpdating = False


'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)

With oMail

'I need this to pull the "to" from B1
.To = Sheets("Sheet1").Range("B1").Value '<--- Settings Sheet name here is Sheet1. Change as needed

'I need this to pull the "Subject" from Settings:A2
.Subject = Sheets("Sheet1").Range("B2").Value '<--- Settings Sheet name here is Sheet1. Change as needed

'I need this to pull the "body" from Settings:B3
.Body = Sheets("Sheet1").Range("B3").Value '<--- Settings Sheet name here is Sheet1. Change as needed

'.Attachments.Add ThisFile '<-- This line is required to add a document as attachment to email.

'NEXT LINE: add the image in hidden manner, position at 0 will make it hidden. This line required for image to show.
.Attachments.Add "C:\Users\logit\Desktop\pics\Bear-stuffs.jpg", oByValue, 0

'Now add it to the Html body using image name
'change the src property to 'cid:your image filename'
'it will be changed to the correct cid when its sent.
.HTMLBody = .HTMLBody & "<br><B>Embedded Image:</B><br>" _
& "<img src='cid:Bear-stuffs.jpg'" & "width='375' height='486'><br>" _
& "<br>Best Regards, <br>Your name here</font></span>"
.Display
End With


'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

vsk17
2018-01-05, 07:17 AM
Hi , Thank you so much for your help....this is working perfectly.

I had another clarification, I have the body of the email in multiple cells. I have tried the below mentioned modification from the above code and it is not taking the range. Kindly help.

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
Set rng = Sheets("mail").Range("B2:B9").SpecialCells(xlCellTypeVisible)
With oMail

.To = Sheets("mail").Range("B1").Value
.Subject = Sheets("mail").Range("B10").Value
.HTMLBody = Sheets("mail").RangetoHTML(rng)
.CC = Sheets("mail").Range("B11").Value

Thanks in advance
Vinay