Send Email As Attachment - Help with Ron De Bruin's Code

We have local holidays here..

I shall get back if there are any problems

Sorry for the late reply.

Thanks

Rashid Khan

For sending files see

http://www.rondebruin.nl/mail/folder2/files.htm

  Regards Ron de Bruin

http://www.rondebruin.nl/tips.htm

"prkhan56" wrote in message news:*** Email address is removed for privacy ***...

Hello All,

I have posted this to microsoft.public.excel.misc group.

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         greg.j <mailto:******@abc.org>... <http://social.answers.microsoft.com/groups/unlock?_done=/group/microsoft.public.excel.misc/browse_thread/thread/be3793069ca23bd7&msg=e3ba8364c22035c6>@abc.org

Allan James bbbb cccc ddd       allan.ja... <http://social.answers.microsoft.com/groups/unlock?_done=/group/microsoft.public.excel.misc/browse_thread/thread/be3793069ca23bd7&msg=e3ba8364c22035c6>@abc.org

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 greg.j... <http://social.answers.microsoft.com/groups/unlock?_done=/group/microsoft.public.excel.misc/browse_thread/thread/be3793069ca23bd7&msg=e3ba8364c22035c6>@abc.org

Allan James bbbb cccc ddd       will be sent to allan.ja... <http://social.answers.microsoft.com/groups/unlock?_done=/group/microsoft.public.excel.misc/browse_thread/thread/be3793069ca23bd7&msg=e3ba8364c22035c6>@abc.org

abcd efgh ijkl mnop                     will not be emailed to anyone

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

I have found this code from Ron's site which I need to change to suit my needs

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 =

"r... <http://social.answers.microsoft.com/groups/unlock?_done=/group/microsoft.public.excel.misc/browse_thread/thread/be3793069ca23bd7&msg=9864bc5aa5ee7a6a>@debruin.nl" <<<< 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

Any help would be appreciated.

TIA

Rashid Khan