extracting emill addresses so I can paste into outlook in the correct format

[markfay]

Hi I have a spreadsheet of 400 names, positions and e-mails which are constantly being updated. I can sort using the filters to get manager, technician etc. Each name has an e-mail in a separate column.
So I can then mail groups of people or all of them as needed I need to extract the e-mail addresses and get them into the format fredbloggs@gmail.com;susanbloggs'gmail.com;henrybloggs@gmail.com
i.e all separated by a semi colon.

Can anybody tell me an easy way of doing this?

many thanks

Mark

 

[Greasy]

So I can understand this better... Are you saying you will use a filter to sort the data, then you want all of the email addresses that have been sorted to be extracted?

If so I would write a macro to open Outlook and create a new email with those email addresses as the send to address. Otherwise if the email address are in say, B2:B10 you can select those cells, copy them individually and paste them somewhere else with a semi colon between like B2 & ";" & B3 or something like that.

 

[markfay]

So I can understand this better... Are you saying you will use a filter to sort the data, then you want all of the email addresses that have been sorted to be extracted?

If so I would write a macro to open Outlook and create a new email with those email addresses as the send to address. Otherwise if the email address are in say, B2:B10 you can select those cells, copy them individually and paste them somewhere else with a semi colon between like B2 & ";" & B3 or something like that.

Thank Greasy, yes if I need just the managers addresses I want to filter those and then have in the format for the e-mail. Or it might be receptionists, technical people or everybody etc.

I can copy the filtered addresses but don't see How I can then convert them, yes B2 & ";" & B3 would work but would be very laborious to do every time. I was hoping there might be some sort of export solution eg as a comma separated file where I could them replace the commas. However I've not found any of doing it.

 

[Greasy]

Shouldn't be too hard to do that. I'm not at my computer right now but when I am later tonight I think I know exactly what will work. We can make it open a new email and automatically put them in the To: field. I will post the code as soon as I find it and modify it. I have it set up a little differently but I can make it work for you.

 

[jjy]

ADVERTISEMENT

Hi I have a spreadsheet of 400 names, positions and e-mails which are constantly being updated. I can sort using the filters to get manager, technician etc. Each name has an e-mail in a separate column.
So I can then mail groups of people or all of them as needed I need to extract the e-mail addresses and get them into the format fredbloggs@gmail.com;susanbloggs'gmail.com;henrybloggs@gmail.com
i.e all separated by a semi colon.

Can anybody tell me an easy way of doing this?

many thanks

Mark

Here are the steps I use when I need to create email lists from Excel:</SPAN></SPAN>

Copy your Excel list of email addresses and paste it into Word. It will paste in as a table (you don't need to copy the column header but if you do, you can just delete it later; you won’t need it)</SPAN></SPAN>

Select All and Convert Table to Text – the result will be a straight list of email addresses with a paragraph mark at the end of each one (¶). (You won’t see the paragraph marks unless you have the option to Show Formatting Symbols turned on. You don't need to see them in order to replace them</SPAN>.)</SPAN></SPAN></SPAN>

Use Find and Replace to find “^p” without the quotes, and replace with “; ” without the quotes. (That says to replace all the paragraph marks,” ¶”, with a semi-colon plus a space; the extra space just helps make it more readable)</SPAN></SPAN>

Click your choice of the Find/Replace buttons. You should then end up with a block of email addresses that can be copied/pasted into your email application.</SPAN></SPAN>
</SPAN></SPAN>
You may want to clean up the results by deleting any unnecessary data (like the column header if you initially copied it or maybe extra spaces or commas at the end of the list</SPAN>).</SPAN></SPAN></SPAN>

Hope this helps.</SPAN></SPAN>

 

[markfay]

Shouldn't be too hard to do that. I'm not at my computer right now but when I am later tonight I think I know exactly what will work. We can make it open a new email and automatically put them in the To: field. I will post the code as soon as I find it and modify it. I have it set up a little differently but I can make it work for you.

Thats very good of you Greasy, greatly appreciated.

 

[Greasy]

ADVERTISEMENT

Ok here is a bit of code that should work for you. Hope this helps. If you don't know how to use it or have trouble with it then let me know.

Public Sub MultiRecEmail()


Dim OutApp As Object
Dim OutMail As Object
*************************************************************************************
'This is set to pull the email addresses in Sheet1 A1:A500, you can change it to whatever you want.
'The code will work as long as the values of those cells are email addresses.                                 
*************************************************************************************
EmailTo = Join(Application.Transpose(Worksheets("Sheet1").Range("A1:A500").Value), ";")
 
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


 'You can add more actions such as attaching files etc. as you need

With OutMail
    .To = EmailTo
    .Subject = "Type Your Subject Here Or Just Delete The Whole Line"
    .Body = "Type Your Body Here Or Just Delete The Whole Line"
    .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing


End Sub

 

[markfay]

Ok here is a bit of code that should work for you. Hope this helps. If you don't know how to use it or have trouble with it then let me know.

Public Sub MultiRecEmail()


Dim OutApp As Object
Dim OutMail As Object
*************************************************************************************
'This is set to pull the email addresses in Sheet1 A1:A500, you can change it to whatever you want.
'The code will work as long as the values of those cells are email addresses.                                 
*************************************************************************************
EmailTo = Join(Application.Transpose(Worksheets("Sheet1").Range("A1:A500").Value), ";")
 
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


 'You can add more actions such as attaching files etc. as you need

With OutMail
    .To = EmailTo
    .Subject = "Type Your Subject Here Or Just Delete The Whole Line"
    .Body = "Type Your Body Here Or Just Delete The Whole Line"
    .Display
End With


Set OutMail = Nothing
Set OutApp = Nothing


End Sub

thank you SO MUCH Greasy, I'll give it a go tonight and let you know how it works

 

[markfay]

Here are the steps I use when I need to create email lists from Excel:

Copy your Excel list of email addresses and paste it into Word. It will paste in as a table (you don't need to copy the column header but if you do, you can just delete it later; you won’t need it)

Select All and Convert Table to Text – the result will be a straight list of email addresses with a paragraph mark at the end of each one (¶). (You won’t see the paragraph marks unless you have the option to Show Formatting Symbols turned on. You don't need to see them in order to replace them.)

Use Find and Replace to find “^p” without the quotes, and replace with “; ” without the quotes. (That says to replace all the paragraph marks,” ¶”, with a semi-colon plus a space; the extra space just helps make it more readable)

Click your choice of the Find/Replace buttons. You should then end up with a block of email addresses that can be copied/pasted into your email application.

You may want to clean up the results by deleting any unnecessary data (like the column header if you initially copied it or maybe extra spaces or commas at the end of the list).

Hope this helps.

this looks a good solution too JJY. Perfect as this is a good general solution for any excel sheet and Greasy has posted some code for my sheet specifically. If both work the answers I've received by joining this forum have been excellent!

 

[markfay]

thank you SO MUCH Greasy, I'll give it a go tonight and let you know how it works

hi Greasy,

tried it but had a problem

when I ran it came up with 'runtime error 91 Object block or with variable not set' and highlighted the 'emailto' line and 'with outmail'

Any ideas?

many thanks

Mark