e-mail loop macro

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
706
Office Version
  1. 365
  2. 2010
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:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
thanks....ill try it soon

i know this is a long shot, but how about incorporating using "use voting buttons" --> "approve; reject"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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!"


 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top