+ Reply to Thread
Results 1 to 2 of 2

Adding a picture to email body with VBA macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2013
    Posts
    121

    Adding a picture to email body with VBA macro

    Hello,

    I am sending emails with my code below. So, I created a string as Email body and I am writing everything there.

    I have two quick questions regarding that:

    First, how can I add a picture from my computer or excel (I can paste the picture to a hidden sheet) to the email body below ?

    Second, How can I make bold my some strings such as Address or StartDate in the email?

    Thank you very very much in advance
    Orhan



    Sub outlookMacro()
    
    Dim StartRow As Integer, EndRow As Integer
    Dim Email_Subject, Email_Send_From, Email_Send_To, _
    Email_Cc, Email_Bcc, Email_Body, firstName, site, StartDate, Address As String
    Dim Mail_Object, Mail_Single As Variant
    
    Email_Send_From = "email"
    Email_Send_To = "email"
    Email_Cc = "email"
    Email_Bcc = "email"
    
    StartRow = InputBox("enter the first record to printt.")
    EndRow = InputBox("enter the last record to print.")
    
    If StartRow > EndRow Then
    Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
    MsgBox Msg, vbCritical, "Advanced Excel Training"
    End If
    
    For i = StartRow To EndRow
    StartDate = Sheets("Sheet2").Cells(i, 3)
    firstName = Sheets("Sheet2").Cells(i, 9)
    Address = Sheets("Sheet2").Cells(i, 18)
    site = Sheets("Sheet2").Cells(i, 6)
    
    Email_Subject = "Welcome in " & site
    
    Email_Body = There should be a picture _
    
    & vbCrLf & "Dear " & Firstname & vbCrLf & "You will start on" & StartDate (Bold)  &  Welcome to our company"  vbCrLf & "Your working location will be " & Address (Bold) 
    
    On Error GoTo debugs
    
    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
    
    .Subject = Email_Subject
    .To = Email_Send_To
    .CC = Email_Cc
    .BCC = Email_Bcc
    .Body = Email_Body
    .Display
    .Send
    End With
    debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    
    Next i
    
    End Sub
    Last edited by orhanceliloglu; 11-24-2016 at 11:38 AM.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,445

    Re: Adding a picture to email body with VBA macro

    Sub sumit()
    Dim mainWB As Workbook
    Dim SendID
    Dim CCID
    Dim Subject
    Dim Body
    Dim olMail As MailItem
    
    Set otlApp = CreateObject("Outlook.Application")
    Set olMail = otlApp.CreateItem(olMailItem)
    Set Doc = olMail.GetInspector.WordEditor
    'Dim colAttach As Outlook.Attachments
    Dim oAttach As Outlook.Attachment
    
    Set mainWB = ActiveWorkbook
    
    SendID = mainWB.Sheets("Mail").Range("B1").Value
    CCID = mainWB.Sheets("Mail").Range("B2").Value
    Subject = mainWB.Sheets("Mail").Range("B3").Value
    Body = mainWB.Sheets("Mail").Range("B4").Value
    With olMail
        .To = SendID
        If CCID <> "" Then
          .CC = CCID
        End If
        .Subject = Subject
        'add the image in hidden manner, position at 0 will make it hidden
        .Attachments.Add "C:\Users\Sumit Jain\Pictures\11\city.jpg", olByValue, 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:city.jpg'" & "width='500' height='200'><br>" _
                    & "<br>Best Regards, <br>Sumit</font></span>"
        .Display
        .Send
    End With
    
    MsgBox ("you Mail has been sent to " & SendID)
    
    End Sub
    I didn't find anything on the net re: making the email Headings Bold. You can use HTML in the body of the message though.

    Maybe someone else has found how to make bold the headings.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy selected range to email body as picture
    By tmzmonko in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2016, 02:08 AM
  2. Mail using VBA with the picture displaying in email body in MS outlook 2010
    By venikumar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2015, 06:45 AM
  3. Voting in outlook by clicking on picture in the email body
    By rajesh.pardhe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2015, 09:02 AM
  4. [SOLVED] sending email with a Picture in the body
    By kiranpat in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-22-2014, 08:31 PM
  5. Need help copying a picture/image from excel to the body of an outlook email
    By kadames27 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2014, 10:17 AM
  6. [SOLVED] Change code to include picture in body of email
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-05-2013, 01:22 PM
  7. copy excel cells into body of email as a picture.
    By faraaz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2012, 11:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1