Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Excel Template With Macro For Sending Mails And Attachment Through Outlook

  1. #1
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0

    Excel Template With Macro For Sending Mails And Attachment Through Outlook

    Dear Experts.

    Is there any templates/ macro available to send mails from excel thru outlook where the address , cC address, subject with attachment stored in a target picking the appropriate file from the location specified in the excel sheet as attachment.

    A smaple work book is attached for your reference .

    This will be a life saver tool to forward emials and save much of the time.


    Thanks in Advance expectinga valuable revert.

    Regards
    R.Ramakrishnan

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 11-30-2023 at 02:58 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi RK,

    Ron has developed a nice Add-in. You could download the add-in from here

    Or if you want templates, see Templates
    Last edited by Admin; 09-09-2011 at 10:23 AM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Using Automation VBA to Send Many Bulk Microsoft Outlook Message

    OR you could download this file which does exactly what you asked for....

    Code:
    Option Explicit
    
    'Ensure that you select the Microsoft Outlook X.0 Object Library in the references
    'Outlook needs to be loaded, and account logged in
    
    Sub CallMailer()
        
        Dim lngLoop As Long 'Programming ethics 1. Always start your first line after leaving a line space, and 1 indentation level
        
        With ActiveSheet
            For lngLoop = 2 To .Cells(Rows.Count, 1).End(xlUp).Row ' Programming ethics 3. Always indent your loops, case statements and with constructors
                Call SendMessage(strTo:=.Cells(lngLoop, 1).Value, strCC:=.Cells(lngLoop, 2).Value, strBCC:=.Cells(lngLoop, 7).Value, strMessage:=.Cells(lngLoop, 8).Value, strSubject:=.Cells(lngLoop, 3).Value, strAttachmentPath:=.Cells(lngLoop, 6).Value, rngToCopy:=.Cells(lngLoop, 9))
            Next lngLoop
        End With 'Programming ethics 2. Always end your last line leaving a line space before ending the sub or function, and having indendation level of 1
        
    End Sub
    
     Sub SendMessage(strTo As String, Optional strCC As String, Optional strBCC As String, Optional strSubject As String, Optional strMessage As String, Optional strAttachmentPath As String, Optional rngToCopy As Range, Optional blnShowEmailBodyWithoutSending As Boolean = False)
    
        Dim objOutlook As Outlook.Application
        Dim objOutlookMsg As Outlook.MailItem
        Dim objOutlookRecip As Outlook.Recipient
        Dim objOutlookAttach As Outlook.Attachment
    
        If Trim(strTo) & Trim(strCC) & Trim(strBCC) = "" Then
            MsgBox "Please provide a mailing address!", vbInformation + vbOKOnly, "Missing mail information"
            Exit Sub
        End If
        ' Create the Outlook session.
        On Error Resume Next
        Set objOutlook = GetObject(, "Outlook.Application")
        Err.Clear: On Error GoTo -1: On Error GoTo 0
        If objOutlook Is Nothing Then
            Set objOutlook = CreateObject("Outlook.Application")
        End If
    
        ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
        With objOutlookMsg
            ' Add the To recipient(s) to the message.
            If Trim(strTo) <> "" Then
                Set objOutlookRecip = .Recipients.Add(strTo)
                objOutlookRecip.Type = olTo
            End If
            
            ' Add the CC recipient(s) to the message.
            If Trim(strCC) <> "" Then
                Set objOutlookRecip = .Recipients.Add(strCC)
                objOutlookRecip.Type = olCC
            End If
    
           ' Add the BCC recipient(s) to the message.
           If Trim(strBCC) <> "" Then
                Set objOutlookRecip = .Recipients.Add(strBCC)
                objOutlookRecip.Type = olBCC
            End If
    
           ' Set the Subject, Body, and Importance of the message.
           If strSubject = "" Then
                strSubject = "This is an Automation test with Microsoft Outlook"
           End If
           .Subject = strSubject
           If strMessage = "" Then
                strMessage = "This is the body of the message." & vbCrLf & vbCrLf
           End If
           .Importance = olImportanceHigh  'High importance
           If Not strMessage = "" Then
            .Body = strMessage & vbCrLf & vbCrLf
           End If
           If Not rngToCopy Is Nothing Then
            .HTMLBody = .Body & RangetoHTML(rngToCopy)
           End If
    
           ' Add attachments to the message.
           If Not IsMissing(strAttachmentPath) Then
                If Len(Dir(strAttachmentPath)) <> 0 Then
                    Set objOutlookAttach = .Attachments.Add(strAttachmentPath)
                Else
                    MsgBox "Unable to find the specified attachment. Sending mail anyway."
                End If
           End If
    
           ' Resolve each Recipient's name.
           For Each objOutlookRecip In .Recipients
               objOutlookRecip.Resolve
           Next
    
           ' Should we display the message before sending?
           If blnShowEmailBodyWithoutSending Then
               .Display
           Else
               .Save
               .Send
           End If
        End With
        
        Set objOutlook = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlookAttach = Nothing
        Set objOutlookRecip = Nothing
        
    End Sub
    
    'http://msdn.microsoft.com/en-us/library/ff519602(v=office.11).aspx#odc_office_UseExcelObjectModeltoSendMailPart2_MailingRangeSelectionBody
    Function RangetoHTML(rng As Range)
    
    ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
    
        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
     
        ' Copy the range and create a workbook to receive the data.
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
     
        ' Publish the sheet to an .htm file.
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
     
        ' Read all data from the .htm file into the RangetoHTML subroutine.
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
     
        ' Close TempWB.
        TempWB.Close savechanges:=False
     
        ' Delete the htm file.
        Kill TempFile
     
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
        
    End Function
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0
    Dear Expert,

    This forum is really a life saver where the solutions are provide immediately. Thanks a million for the wonderful solution proivdied to me spedning your very valuable time.
    This is not only saves my time but also helps me to complete the tasks withing the TAT. I salute you for your help. I need to try this in my office computer where office 2003 is intstalled. Hope this will be compactable with office 2003 also.



    Thanks a million

    Regards
    R.Ramakrishnan

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Ramakrishnan, If your office installations have the latest patches and add-ons, it should be able to open the Office 2007+ files. In case you are not able to, I recommend saving the file as 1997-2003 format, and then use it.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0
    Sir,

    I have installed the latest office 2003 sp3 & the file format converters from microsoft but there was 2 errors observed

    it opened a window with compile error "can't find project or library" with the highlighted line "


    Yellow colour highlighted in the below code

    Sub SendMessage(strTo As String, Optional strCC As String, Optional strBCC As String, Optional strSubject As String, Optional strMessage As String, Optional strAttachmentPath As String, Optional blnShowEmailBodyWithoutSending As Boolean = False)

    Dim objOutlook As Outlook.Application

    If time permists please asssist. We use office 2003 in office.

    Thanks and Regards
    R.Ramakrishnan

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Goto the VBE window, tools>>references>> remove the missing reference library and select Microsoft Outlook 11.0 Object Library
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    One could use Late Binding, and work independent of the library being added as reference, but for the sake of performance, I have used Early Binding, which requires the reference library to be added.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    Sep 2011
    Posts
    13
    Rep Power
    0
    Sir,

    I installed the office Compactability pack also but unfortuntely the XLSM file is not working. Is it possible to convert it inot xla format.

    is there any work around so that i can use the sheet i give to you.

    REgards
    R.Ramakrishnan

  10. #10
    Junior Member
    Join Date
    Jun 2012
    Posts
    5
    Rep Power
    0
    Dear Expert,
    Thanks a lot for this macro, really its doing magic. But i need your help to do some changes in this code according to my requirement.
    i want body of the message should reflect as below in the email.

    "Hi (Name, which is in the first column)

    Please find the attached claims report for last week.
    if you have any question please contact me@klm.com

    Regards
    ABC"

    (As per your macro,greeting line,body of the message,regards all coming in a single line)

    So help me to get it done,if you send updated code my personal id that will be great

    my E mail id-muralikusagur@ymail.com

    thanks in Advance.....................

Similar Threads

  1. Replies: 6
    Last Post: 06-05-2013, 11:33 PM
  2. Replies: 4
    Last Post: 03-01-2013, 10:48 PM
  3. Replies: 7
    Last Post: 08-08-2012, 10:24 AM
  4. Replies: 7
    Last Post: 05-09-2012, 11:34 PM
  5. Replies: 3
    Last Post: 02-20-2012, 12:54 AM

Tags for this Thread

Posting Permissions

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