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