Results 1 to 4 of 4

Template & Attachment - Send with Excel Macro

Threaded View

  1. #2
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: Template & Attachment - Send with Excel Macro

    Good time of day, amitbhatt!

    I've solved very similar task on Russian forum. In the attachment you will find macro which allow you to form email via Excel with pictures and attachments. Code is nicely commented, so it is almost easy to modify for your needs.

    Hope this sample will help. Please ask if you have additional questions.

    Sub Send_Email()
    
    'From http://vba-useful.blogspot.ru/2014/01/send-html-email-with-embedded-images.html
    'Reorganized and edited by Roman Rioran Voronov at 30.06.2014 | voronov_rv@mail.ru
    
    With Application 'Shutting down some graphical options to increase programm speed
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Dim OutApp As Object 'To call Outlook Application
    Dim OutMail As Object 'To work with a new Outlook message
    Dim TempFilePath As String 'Path to store objects
    
    TempFilePath = Environ$("temp") & "\"
    
    Set OutApp = CreateObject("outlook.application") 'Create a new Microsoft Outlook session
    Set OutMail = OutApp.CreateItem(0) 'Create a new message
    
    With OutMail
    
        .Subject = "Call-Center Daily Report" 'So called (by myself) head of letter.
        .To = ThisWorkbook.Sheets(1).Cells(18, 1).Value 'Who will receive our message.
    
        'First text part of the message.
        .HTMLBody = "<span LANG=EN>" & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
            & "Hello, Mr. Scrooge McDuck!<BR><BR>Please be informed about our yeasturdays success.<BR>Call-center was operating as it shown below:<BR>"
            
        '== Insert an object 1 ==
        Call Get_Pic("Graphics") 'Time to create the image as a JPG file
        .Attachments.Add TempFilePath & "Graphics.jpg", 0, 0
        
        'Let us combine object 1 with a body of message
        .HTMLBody = .HTMLBody & "<BR>" & "<img src='cid:Graphics.jpg'" & "<BR>" _
            & "<BR>And here are details with used numbers down below:<BR>"
            
        '== Insert an object 2 ==
        Call Get_Txt("Details") 'Time to create the image as a JPG file
        .Attachments.Add TempFilePath & "Details.jpg", 0, 0
    
        'Let us combine object 2 with a body of message
        .HTMLBody = .HTMLBody & "<BR>" & "<img src='cid:Details.jpg'" & "<BR>" _
            & "<BR>" & ThisWorkbook.Sheets(1).Cells(21, 1).Value & "<BR><BR><BR><B>Best Regards,<BR>Roman Rioran Voronov</B></font></span>"
        
        .Display 'Choose .Send if you want message to be sended automatically without displaing
        
    End With
    
    Set OutApp = Nothing
    Set OutMail = Nothing
    
    With Application 'Turning graphical options back
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    End Sub
        
    Sub Get_Pic(NameFile As String)
    
    Dim PlaceX As Shape
    
    ThisWorkbook.Worksheets(1).Activate 'Selecting sheet with future screenshot
    
    Set PlaceX = ThisWorkbook.Worksheets(1).Shapes(1) 'Selecting exact place to catch
    PlaceX.CopyPicture
    
    With ThisWorkbook.Worksheets(1).ChartObjects.Add(PlaceX.Left, PlaceX.Top, PlaceX.Width, PlaceX.Height)
        .Activate
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & NameFile & ".jpg", "JPG"
    End With
    
    Worksheets(1).ChartObjects(Worksheets(1).ChartObjects.Count).Delete
        
    Set PlaceX = Nothing
    
    End Sub
    
    Sub Get_Txt(NameFile As String)
    
    Dim PlaceY As Range
    
    ThisWorkbook.Worksheets(1).Activate 'Selecting sheet with future screenshot
    
    Set PlaceY = ThisWorkbook.Worksheets(1).Range("A1:D9") 'Selecting exact place to catch
    PlaceY.CopyPicture
    
    With ThisWorkbook.Worksheets(1).ChartObjects.Add(PlaceY.Left, PlaceY.Top, PlaceY.Width, PlaceY.Height)
        .Activate
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & NameFile & ".jpg", "JPG"
    End With
    
    Worksheets(1).ChartObjects(Worksheets(1).ChartObjects.Count).Delete
        
    Set PlaceY = Nothing
    
    End Sub
    Attached Files Attached Files
    Best wishes and have a nice day!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Send excel file as attachment using gmail in macro?
    By fourmurphys in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-20-2019, 09:21 PM
  2. Macro to send an attachment via Outlook from excel
    By dobracik in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2014, 04:32 AM
  3. [SOLVED] Macro to send a pdf attachment via outlook from excel
    By Josiah in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-26-2013, 02:17 PM
  4. VBA Code: MACRO to send lotus notes email with excel attachment
    By FLani in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 10:23 AM
  5. Excel Template using "send to" as attachment
    By Dreammy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-28-2007, 05:50 PM

Tags for this Thread

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