how to generate email list for Outlook from Excel cells

[Emanu88]

I have a contact list created in Excel, on each row I have different contact
information such as name, business unit, phone and email address.

What I want to do:
When filtering on a certain business unit from this table I would like to
generate in Outlook the email list of this business unit to send an email to
all the persons of these business unit.

Please let me know how I can make that happen.

Thanks,

Emmanuel

 

[Ron de Bruin]

Hi Emanu88

Try this sample for Outlook
This will use all the visible cells in A1:A10 in "Sheet1"
Change it to your sheet and range

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim cell As Range
Dim strto As String

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

On Error Resume Next
For Each cell In
ThisWorkbook.Sheets("Sheet1").Range("A1:A10").SpecialCells(xlCellTypeVisible)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Drew]

Hi Emanu88

Try this sample forOutlook
This will use all the visible cells in A1:A10 in "Sheet1"
Change it to your sheet and range

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim cell As Range
    Dim strto As String

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

    On Error Resume Next
    For Each cell In
ThisWorkbook.Sheets("Sheet1").Range("A1:A10").SpecialCells(xlCellTypeVisibl­e)
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    On Error GoTo 0
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = strto
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

--

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








- Show quoted text -

Hello my name is Drew and i have a similiar question.

I have a list of contacts that I would like to send a general email
to. The subject will be the same as will most of the text. I would
like to write a Macro that would fill the "To" field from one column,
then fill the constant subject line, then fill in the contacts name
and company name into the text of the email. Then I would like the
macro to repeat this process down the column.

Thanks in advance,
Drew

 

[Ron de Bruin]

Hi Drew

Try this one
http://www.rondebruin.nl/mail/folder3/message.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Emanu88

Try this sample forOutlook
This will use all the visible cells in A1:A10 in "Sheet1"
Change it to your sheet and range

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim cell As Range
Dim strto As String

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

On Error Resume Next
For Each cell In
ThisWorkbook.Sheets("Sheet1").Range("A1:A10").SpecialCells(xlCellTypeVisibl­e)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

--

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








- Show quoted text -

Hello my name is Drew and i have a similiar question.

I have a list of contacts that I would like to send a general email
to. The subject will be the same as will most of the text. I would
like to write a Macro that would fill the "To" field from one column,
then fill the constant subject line, then fill in the contacts name
and company name into the text of the email. Then I would like the
macro to repeat this process down the column.

Thanks in advance,
Drew

 

[Drew]

Hi Drew

Try this onehttp://www.rondebruin.nl/mail/folder3/message.htm

--

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













Hello my name is Drew and i have a similiar question.

I have alistof contacts that I would like tosenda general email
to. The subject will be the same as will most of the text. I would
like to write a Macro that would fill the "To" field from one column,
then fill the constant subject line, then fill in the contacts name
and company name into the text of the email. Then I would like the
macro to repeat this process down the column.

Thanks in advance,
Drew- Hide quoted text -

- Show quoted text -

Thank you so much for the help.