+ Reply to Thread
Results 1 to 1 of 1

macro to add attachments to an email from list

Hybrid View

  1. #1
    Registered User
    Join Date
    10-19-2023
    Location
    miami fl
    MS-Off Ver
    not sure
    Posts
    6

    Exclamation macro to add attachments to an email from list

    Hi,
    I'm new to macros and I'm trying to compile together a code that will allow me to send mass emails with personalized bodies and attachments. I would like two attachments to be in every single email, and to be able to add as many invoices each client has in that email as well. The minimum is one and it can go up to 7. The problem is when I run the code it shows an error because some rows have only one attachment while others may have two or three.

    I show the current code below any help is highly appreciated! Thank you so much.
    
    Sub sendCustEmails()
    
        Dim objOutlook As Object
        Set objOutlook = CreateObject("Outlook.Application")
        Dim objEmail As Object
        
        intRow = 2
        strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text
        
        While (strClientID <> "")
        
            Set objEmail = objOutlook.CreateItem(olMailItem)
        
            strMailSubject = ThisWorkbook.Sheets("Mail_Details").Range("A2").Text
            strMailBody = ThisWorkbook.Sheets("Mail_Details").Range("B2").Text
            strMonth = ThisWorkbook.Sheets("Mail_Details").Range("C2").Text
            
            strfolder = "C:\Users\yshriki\OneDrive - GLSC & Company\Desktop\Invoices"
            strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text
            strEmail = ThisWorkbook.Sheets("Client_Data").Range("B" & intRow).Text
            strCC = ThisWorkbook.Sheets("Client_Data").Range("C" & intRow).Text
            strAmount = ThisWorkbook.Sheets("Client_Data").Range("C" & intRow).Text
            strfilename = ThisWorkbook.Sheets("Client_Data").Range("D" & intRow).Text
            strfilename2 = ThisWorkbook.Sheets("Client_Data").Range("D" & intRow).Text
        
            
            strMailSubject = Replace(strMailSubject, "<ClientID>", strClientID)
            strMailBody = Replace(strMailBody, "<MONTH>", strMonth)
            strMailBody = Replace(strMailBody, "<Amount>", strAmount)
            
            ' to run only when cell is not blank in column B introw
            
            With objEmail
                .To = CStr(strEmail)
                .Subject = strMailSubject
                .Body = strMailBody
                
                If found Then
                .Attachments.Add strfilename
                .Attachments.Add strfilename2
                End If
                
            
                .Attachments.Add "C:\Users\yshriki\OneDrive - GLSC & Company\Desktop\Invoices\Credit_Card_Authorization_Form_!.pdf"
                .Attachments.Add "C:\Users\yshriki\OneDrive - GLSC & Company\Desktop\Invoices\Wire Transfer Instructions.pdf"
                .Send
            End With
            
            
            intRow = intRow + 1
            strClientID = ThisWorkbook.Sheets("Client_Data").Range("A" & intRow).Text
        Wend
        
        MsgBox "Done"
    
    End Sub
    Last edited by davesexcel; 10-25-2023 at 08:15 AM.

+ 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. Macro to send emails with different attachments and table in the email.
    By aashish.shetty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2022, 05:36 AM
  2. Email/Attachments Copy and Register through Excel Macro
    By SAI80 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2020, 07:26 AM
  3. Macro for Email attachments from excel list fails when first line is " "
    By jgriffi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2019, 03:23 PM
  4. [SOLVED] Macro to email XL attachments (files are not attached)
    By pixifaery in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2013, 06:18 PM
  5. Replies: 0
    Last Post: 02-21-2013, 04:46 AM
  6. Excel Macro to download email attachments from MS Outlook.
    By abhay_547 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-06-2010, 02:10 PM
  7. email several attachments (change to ron's macro?)
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2005, 11:05 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