Sending E-mails using Excel Contacts-list

E

Excelboy_01

New Member
Joined
Aug 5, 2011
Messages
32
Hello everyone,

I have a seemingly simple but tricky problem and I was wondering if anyone of you could help me out. Basically, I have an excel database (worksheet) that contains different information about different individuals. It also have a column (say Column Y ), which contains their respective emails addresses.

They have been grouped into different groups. I would like to be able sort out (using say AutoFilter) a group of people and send them an email (preferable personalized email) without going through copying/pasting individual email addresses from excel to outlook. Does anyone have an idea how I can do this?

Thanks for your time and help in advance

PS: I am using Office 2003

Moses
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Hi Moses,

You will need to use copy and paste if using autofilter (I myself have tried alot but failed without it). I would make a sheet that is used as a Temp area.

I will post some code later once I find the workbook I used it in but the steps are.

Autofilter by the criteria you need, Copy the visible cells from the results to temp page (only need to copy Coloum Y), Create email with email address from Temp workbook.

Once again sorry i havent got the code, trying to find it :)
 
Upvote 0
On re-reading your post I have got the wrong end of the stick so ignore my effort.

Hi,

Try this code.
It will send a separate mail for each contact in the column if it isn't hidden by a filter or a blank cell.

You will need to change the column for the email addresses. If you have the names in another column it can use that.

Code: 
Sub Test_Mail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
 
    For Each cell In Rng.Rows
    RowNum = cell.Row 'get row number for use with name
    If Not (cell.Hidden) Then
        Match = True
            If cell.Value <> "" Then
    SendTo = cell.Value
 
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 
            With OutMail
                .To = SendTo
                .Subject = "Test mail"
                .Body = "Hi" & Range("A" & RowNum).Value ' Get name from another column same row
              '  .Attachments.Add attFile
                .Display  'Or use Send
            End With
            Set OutMail = Nothing
        End If
    End If
    Next cell
End Sub
 
Last edited:
Upvote 0
Mail merge will do as you wish, but the criteris will be determined by your columns choices
 
Upvote 0
Thanks for your efforts and advice. I am still trying to figure out to make that code work. If I manage I will let you know.
 
Upvote 0
Sub Test_Mail()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Set Rng = Range(Range("D9"), Range("D" & Rows.Count).End(xlUp))
'creates a loop starting from I9 down column D till there are no more email addresses

For Each cell In Rng.Rows
RowNum = cell.Row 'get row number for use with name
If Not (cell.Hidden) Then 'hidden cells should be excluded
Match = True
If cell.Value <> "" Then
SendTo = cell.Value

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

With OutMail
.To = SendTo
.Subject = "Requesting information about " ' should get 'additions phrase from cell B5
.Body = "Hallo " & Range("G" & RowNum).Value & Range("H" & RowNum).Value ' Get name from another column same row i.e. firstname, Familyname
'.Attachments.Add 'attFile
.Display 'Or instead use
'.Send (sends emails without review)
End With
Set OutMail = Nothing
End If
End If
Next cell
End Sub
.
Click to expand...
Thumbs up everybody for all your suggestions.
I have made minor additions to DAVERUNT's code and seems to be doing my tasks well. But I have two areas that I would like to improve and can't seem to manage
1) on .Subject = "Requesting information about "
I would like it to get an additions phrase from cell B5. i.e. Since Im requesting for information about different people/things. So depending on what is Cell B5. Example: if I have Mary (in B5), the Subject should be: Requesting information about Mary. If I have James then should be: Requesting information about James

2) .Body = "Hallo " & Range("G" & RowNum).Value & Range("H" & RowNum).Value

I would like to add some extra 2 - 3 lines of text. (General to all recipients) like:

"Could you send me this information in the couple of weeks. It is important that we talk before the end of the month. Thanks for your cooperation"

Thanks again
 
Upvote 0
Modified

Code: 
.Subject = "Requesting information about " [B]& Range("B5").Value[/B]
 
 
[B]strTxt[/B]="Could you send me this information in the couple of weeks. It is important that we talk before the end of the month. Thanks for your cooperation"
 
.Body = "Hallo " & Range("G" & RowNum).Value & Range("H" & RowNum).Value [B]& vbNewline & strTxt[/B]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Send e-mails selected from a list in excel
Replies
1
Views
238
Sequoyah
Sequoyah
R
  • Question Question
Scheduled send using mail merge
Replies
1
Views
55
reseng
R
C
  • Solved
Add a table to e-mail to be sent to multiple addreses
Replies
3
Views
266
mumps
mumps
S
  • Question Question
VBA sending an email of two conditions are met
Replies
5
Views
243
mumps
mumps
N
  • Question Question
VBA: Automatically send emails in a loop with different attachments
Replies
3
Views
328
cwstudio2020
C

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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