Excel Macros sending attachments to multiple e-mail addressescontained in one cell.

  • Thread starter Thread starter mason.bancroft
  • Start date Start date
M

mason.bancroft

Hi there!

I have this Excel Macros which is designed to send an attachment to
someone's e-mail address. In this case, the title of the attachment is
identical to the name of the person to who I am sending it to.

My active worksheet only uses two ranges, A and B. Down column A
lists the names of people; each cell in column A contains one person's
name. Down Column B lists each person's corresponding e-mail address;
each cell in column B contains one e-mail address. The code below
works just fine for that.

Unfortunately, there may be more than one e-mail addresses contained
in each B cell. Any suggestions?

Sub SendEmailWithAttachment()
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim strEmail As String, strName As String
Dim lRowCount As Long


Set objOutlook = CreateObject("outlook.application") ' Start
outlook


lRowCount = 2 ' Change to starting ROW containing email address/
name


Do Until ActiveSheet.Cells(lRowCount, 2) = "" ' check for email,
End if none found
strEmail = ActiveSheet.Cells(lRowCount, 2).Value ' get email
address
strName = ActiveSheet.Cells(lRowCount, 1).Value ' get client name
Set objOutlookMsg = objOutlook.CreateItem(olMailItem) ' create
new email msg
With objOutlookMsg ' Fill email


.Subject = "Put this text in subject line" ' Note: Could be
column "C" - ActiveSheet.Cells(lRowCount, 3).Value
.Body = "Put this text in body of email" ' Note: Could be
column "D" - ActiveSheet.Cells(lRowCount, 4).Value

.To = strEmail
.Attachments.Add ("c:/e-mail attachments/" & strName & ".xls")
.Send
End With
lRowCount = lRowCount + 1 ' Increment Row Counter
Loop


objOutlook.Quit
Set objOutlook = Nothing
Set objOutlookMsg = Nothing
End Sub
 
Do you have duplicate rows with the same mail addresss in B ?
Am I understand you correct ?
 
Do you have duplicate rows with the same mail addresss in B ?
Am I understand you correct ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm















- Show quoted text -
Click to expand...

I figured it out Ron. Sorry to waste your time. The code works just
fine. There could be more than one e-mail address in each b cell in
some circumstances. When I was running some tests, it seemed to work
if I did not use a hyper link (i.e., just had the e-mail address in
simple text) and used the semi colon ; to separate each e-mail address
contained in one B cell. If it doesn't work the next time I try, then
I'll leave a message again (but I think it will). Thanks again
Ron! :)
 
Hi,
I need a complex macro for sending multiple mails from excel. I have no knowledge of macros and i would be really obliged if someone can help me with it. Here is a sample excel and the required mails

HeadingID 1ID 2DateEmail IDTABC100110129/13/2011(e-mail address removed); (e-mail address removed); (e-mail address removed)[/COLOR]; xyz@xyz.com; (e-mail address removed)[/COLOR]; xyz@xyz.com; (e-mail address removed)[/COLOR]; xyz@xyz.com; (e-mail address removed)[/SIZE]



Sample mails:

Mail1:
To: (e-mail address removed); (e-mail address removed);
Subject: TABC

Hi,
This is the mail content
100110129/13/20111034102810/13/2011


Mail 2:
To: pqr(e-mail address removed); xyz(e-mail address removed); (e-mail address removed);
Subject: TA00

Hi,
This is the mail content

102110459/14/2011100910189/16/20111082109910/14/2011


Similarly Mail 3, Mail 4 etc till the end of the worksheet. Each mail will begin and end on the basis of column "Heading" and the content of the mail will also depend on that. Please give me a complete macro since i have no knowledge of macros. This macro will be very beneficial for me. i will be really grateful to whoever can give me the complete Macro.

thanks,
Supal
 
Hi,
I need a complex macro for sending multiple mails from excel. I have no knowledge of macros and i would be really obliged if someone can help me with it. Here is a sample excel and the required mails

HeadingID 1ID 2DateEmail IDTABC100110129/13/2011(e-mail address removed); (e-mail address removed); (e-mail address removed)[/COLOR]; xyz@xyz.com; (e-mail address removed)[/COLOR]; xyz@xyz.com; (e-mail address removed)[/COLOR]; xyz@xyz.com; (e-mail address removed)[/SIZE]



Sample mails:

Mail1:
To: (e-mail address removed); (e-mail address removed);
Subject: TABC

Hi,
This is the mail content
100110129/13/20111034102810/13/2011


Mail 2:
To: pqr(e-mail address removed); xyz(e-mail address removed); (e-mail address removed);
Subject: TA00

Hi,
This is the mail content

102110459/14/2011100910189/16/20111082109910/14/2011


Similarly Mail 3, Mail 4 etc till the end of the worksheet. Each mail will begin and end on the basis of column "Heading" and the content of the mail will also depend on that. Please give me a complete macro since i have no knowledge of macros. This macro will be very beneficial for me. i will be really grateful to whoever can give me the complete Macro.

thanks,
Supal
 
Back
Top