e-mail loop macro

daveyc18

Active Member
so I have the filename in column A, and e-mail address in column B and C

column A
a_Cass,Mass_Chen,Victor_

column B
cass.mass@x.com

column C

chen.victor@x.com


.....

is it possible to make an e-mail macro that will loop through each filename listed in column A and create an e-mail for that and attach the file name and insert the appropriate e-maill addresses in the "To" and "CC" fields

so in my example, an e-mail is created, the filename "a_cass,mass_chen,victor_" is attached ; cass.mass@x.com is put in the "To" field'; and chen.victor@x.com is put in the "CC" field ...."subject" = the filename & " leadsheet"

after that....the macro looks at the next row and creates another e-mail accordingly
 
Last edited:

DanteAmor

Well-known Member
Try this:

Code:
Sub e_mail_loop()
    Dim i As Long, dam As Object, wfile As String
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Set dam = CreateObject("Outlook.Application").CreateItem(0)
        dam.To = Range("B" & i).Value
        dam.Cc = Range("C" & i).Value
        dam.Subject = "Write the subject here"
        dam.Body = "Write the body here"
        wfile = ThisWorkbook.Path & "\" & Range("A" & i).Value
        If Dir(wfile) <> "" Then
            dam.Attachments.Add wfile
        End If
        dam.Display
    Next
End Sub
 

daveyc18

Active Member
thanks....ill try it soon

i know this is a long shot, but how about incorporating using "use voting buttons" --> "approve; reject"
 

daveyc18

Active Member
how about a check where....it'll loop the the FOLDER containing all the PDF files (attachments)....and then if it's missing from the list in the spreadsheet...the macro warns that the spreadsheet does not contain a filename that's stored in the folder

note that I want to IGNORE the last 7 characters of the filename in the folder
 

DanteAmor

Well-known Member
how about a check where....it'll loop the the FOLDER containing all the PDF files (attachments)....and then if it's missing from the list in the spreadsheet...the macro warns that the spreadsheet does not contain a filename that's stored in the folder

note that I want to IGNORE the last 7 characters of the filename in the folder
If you explain clearly how you have the data, maybe I can help you.
 

daveyc18

Active Member
also....in the VBA code...if it does NOT detect the filename in the folder , don't display the e-mail and move onto the next row
 
Last edited:

daveyc18

Active Member
eg

next month, there's a file called a_BACK,a_FRONT, but now it's missing in column A because it's new, so I want the macro to warn and say "you're missing a_BACK,a_FRONT in column A; add it!"


 

DanteAmor

Well-known Member
also....in the VBA code...if it does NOT detect the filename in the folder , don't display the e-mail and move onto the next row
Try this

Code:
Sub e_mail_loop()
    Dim i As Long, dam As Object, wfile As String
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        wfile = ThisWorkbook.Path & "\" & Range("A" & i).Value
        If Dir(wfile) <> "" Then
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = Range("B" & i).Value
            dam.Cc = Range("C" & i).Value
            dam.Subject = "Write the subject here"
            dam.Body = "Write the body here"
            dam.Display
            dam.Attachments.Add wfile
        End If
    Next
End Sub
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top