Create Outlook Distribution lists (default txt file) with excel

[nicolascap]

Hello experts,

I have an excel file with several contacts. For each contact there is a
"first name" and "last name" column.

- First of all I would like to concatenate these into a "email adress"
column which gives a text string such as:
"first name"."last name"@excelforum.com

What could be used to do that automatically?

Then, as it is shown in the example, each of the contacts belong to
different groups which will be emailed specifically. By entering in
each "group X" column a text value ("To" for main recipient and "Cc"
for Carbon Copy Recipient), I would like to generate an email for any
given group. Or by Default a txt file that I'll copy and paste
afterwards. From my own view, it can be done with a press button on top
of each column to generate that column specific email. But I am not the
expert here.

Though it would be easier, I can't use a regular outlook distribution
list since those lists always change and excel will be easier to use in
order to manage main recipients and Carbon Copy Recipients.

I hope I have been clear enough.

Thx for your help.

Cheers

Nico


+-------------------------------------------------------------------+
|Filename: Contact ist example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4405 |
+-------------------------------------------------------------------+

 

[Ron de Bruin]

Hi Nico

=A1&"."&B1& "@excelforum.com"
copy down

Start here for mail code
http://www.rondebruin.nl/sendmail.htm

 

[nicolascap]

thanks Ron,

Maybe I am getting it all wrong but this add-in I already have in Excel
("file">>"Send To"...).

I want to use the current workseet, to email an other file (not
necessarily an excel file) to some of the filtered recipients in this
same worksheet...

Nico

 

[Ron de Bruin]

There are code examples on my site that loop through a list in Excel and use that in the mail
Check out the code examples and not the add-in

If you make values of the formula I give you you can use code like this for the To line in the
Outlook examples.
http://www.rondebruin.nl/mail/tips2.htm

 

[nicolascap]

OK

I managed to use the code but my problem isn't cleared since I wante
to use Autofilter on "To" or "Cc" for each group to be able to generat
different distribution lists with only the filtered recipients appearin
in the "To" field of the email.

Here I email my whole list...

Attached updated version of the example.

Cheers

nic

+-------------------------------------------------------------------
|Filename: Contact Example 2.zip
|Download: http://www.excelforum.com/attachment.php?postid=4414
+-------------------------------------------------------------------

 

[Ron de Bruin]

Hi nicolascap

You can test the visible cells in the loop

Dim cell As Range
Dim strto As String
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.EntireRow.Hidden = False And cell.Value Like "*@*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)