e-mail loop macro

[daveyc18]

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

 

[DanteAmor]

Try this:

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]

thanks....ill try it soon

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

 

[daveyc18]

nvm got it

 

[daveyc18]

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]

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]

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

 

[daveyc18]

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]

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

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