Match Email address to Filenames in a list and Send Email As Attachment

  • Thread starter Thread starter prkhan56
  • Start date Start date
P

prkhan56

Hello All,
I am using Excel 2007.

I have an Excel Sheet as follows:
Col A has File Name. Col B is having email address.

Col A Col B
Greg John xxxx yyyy zzz (e-mail address removed)
Allan James bbbb cccc ddd (e-mail address removed)
abcd efgh ijkl mnop NA
….
…
...
As seen from the data Col A is having file names beginning with
the user name. Where there is no user name, the Cells in Col B have
NA. The list is long
I wish to have a macro match the names from the Email address in Col B
and search
in Col A and email that particular file as attachment to all names in
ColB ...NA will be ignored.

Eg. (in above case)
Greg John xxxx yyyy zzz will be sent to (e-mail address removed)
Allan James bbbb cccc ddd will be sent to (e-mail address removed)
abcd efgh ijkl mnop will not be emailed to anyone

The path of the files is: C:\Data\Reports

Hope to have some help
TIA
Rashid Khan
 
Hello All,
I am using Excel 2007.

I have an Excel Sheet as follows:
Col A has File Name.    Col B is having email address.

Col A                                  Col B
Greg John xxxx yyyy zzz (e-mail address removed)
Allan James bbbb cccc ddd       (e-mail address removed)
abcd efgh ijkl mnop                     NA
….
…
..
As seen from the data Col A is having file names beginning with
the user name. Where there is no user name, the Cells in Col B have
NA.  The list is long
I wish to have a macro match the names from the Email address in Col B
and search
in Col A and email that particular file as attachment to all names in
ColB ...NA will be ignored.

Eg. (in above case)
Greg John xxxx yyyy zzz will be sent to (e-mail address removed)
Allan James bbbb cccc ddd       will be sent to (e-mail address removed)
abcd efgh ijkl mnop                     will not be emailed to anyone

The path of the files is: C:\Data\Reports

Hope to have some help
TIA
Rashid Khan
Click to expand...

The macro recorder can be your frient. Try recording doing this
manually and then clean it up and incorporate into a loop.
 
The macro recorder can be your frient. Try recording doing this
manually and then clean it up and incorporate into a loop.- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Don,
I have gone through various posts in the newsgroup (which was not
helpful for my need) and also found the following code from
Ron's site but I dont know how to modify the <<<<< >>>>> commented
below to suit my needs and loop through the whole list

Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010,
Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook
object .
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
' Change the mail address and subject in the macro before you run
it.
With OutMail
.To =
"(e-mail address removed)" <<<<
This need to be changed to the names in Col B as per my original post.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hello World!"
.Attachments.Add
ActiveWorkbook.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:
\test.txt") <<<< the filename should
match from the path with the email address in Col B >>>>>>>

' In place of the following statement, you can use ".Display"
to
' display the mail.
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Additional to the above...it should not send email where the Cell
value is NA in Col B.

Hope to get some help on the above.
Thanks for your time.
Regards

Rashid
 
Back
Top