sending email to multiple recipients

ny92mike

New Member
Joined
Mar 28, 2006
Messages
40
hello all,

I would like to create a hyperlink or a button to send an email to all of my recipients. I started out with using the formula

=hyperlink(concatenate("mailto:",A1,", ",A2),"email all")

but I have ran into the problem that my email list exceeds the amount the formula accepts (14).

Any answers to this is greatly appreciated.

Thanks
Mike
 
Oh $#((#(- double post - apologies to the Board Powers that Be - my browser timed out and I hit refresh 'stead of stop

Terhune5 - ping me ofline if you need any assistance - kinda busy day for me but I am glad to assist - I have gotten so much from this board that I can finally give somethin' back!
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hey double posters. (yes, I have been there too)
Only you or the moderator can remove those double posts.
Click Edit, there will be a Delete option near the bottom of the screen.
Confirm with Yes.
Or, just click the "X" from the forum on your post.
 
Upvote 0
Thanks DatSmart - Tried and there was no option - looked in the FAQ & saw: "Please note that normal users cannot delete a post once someone has replied."

Oh well.... at least this confirms I am normal :p

Cheers!
 
Upvote 0
Nixter,

thanks for taking the time to help me out with this problem.

I added the code by coping and pasting, I changed the range value to (b1:b64) but I am still getting any error message (subscript out of range) I think its because of the sheet name but I'm not sure, the list of email addresses in a sheet called "email".

thanks again
Mike
 
Upvote 0
Mike - sent you a private message w/ my e-mail included - feel free to send your Excel sheet and I will review

Cheers!
 
Upvote 0
Mike - sent you a private message w/ my e-mail included - feel free to send your Excel sheet and I will review

Cheers!
 
Upvote 0
I would like to create a hyperlink or a button to send an email to all of my recipients. I started out with using the formula
=hyperlink(concatenate("mailto:",A1,", ",A2),"email all")
This infers your recipient addresses are in the active sheet starting in cell A1, such as:
anyone@anywhere.com
someone@somewhere.com
thatone@thatthere.com

If the issue is that you want to send one email with those names all together in the same "To" field using Outlook, the following code would do that. Be sure to go into the VBE beforehand and establish a reference to Microsoft Outlook 11.0 Object Library (where 11.0 depends on the version of Office you are using).



Option Explicit
Sub myEmail()

'Prepare Excel
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False

'Declare and define variables
Dim objol As New Outlook.Application
Dim objmail As MailItem
Dim strTo$
Dim strToFinal
Dim strSubject$
Dim strBody$
Dim i%
strTo = "": i = 1
strSubject = "Test of multiple recipients"
strBody = "Hello everyone, this is a test."

'Loop to identify who should get the emails
Do
strTo = strTo & Cells(i, 1).Value & "; "
i = i + 1
Loop Until IsEmpty(Cells(i, 1))
strToFinal = Mid(strTo, 1, Len(strTo) - 2)

'Define the Outlook object variables
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)

'Display the email message
With objmail
.To = strToFinal
.Subject = strSubject
.Body = strBody
.Display 'Change to Send
End With

'Release object variables from system memory
Set objmail = Nothing
Set objol = Nothing

'Restore Excel
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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