e-mail to multiple recipients

[Bukol]

Here is a good one:
I have a list of e-mail addresses in one column.
Question one: how do I convert them (it's text) into a hyperlink without having to type every address by hand?
Or question two: How can I automate to send a separate e-mail message to everyone on that list using some text stored in another sheet of that workbook. I dont want to create a group of recipients, but send single messages.
Hans

 

[Bukol]

Al,
Your last code works!
Two bugs though:
How do I control the number of loops? - It seems to run endless.
And everytime it has sent an e-mail it gives me the message "E-Mail sent" and I have to respond. Where to put a "hide message" and how to formulate?
Hans

 

[Al Chara]

It should only run for the number of email addresses in column A (There has to be more than just 1). Delete the line msgbox "Email Sent" to get rid of the box. I just put it there to make sure that it doesn't loop before the mail is sent. That is the bad thing about using SendKeys, which is why I try to avoid using it. Sometimes you just have to use it.

To make sure that it runs correctly without a msgbox. Replace the msgbox line with the following:
Application.Wait (Now + TimeValue("0:00:02"))
You can play around with both instances of this code to see which combination works best.

The line of code just pauses the macro, so it really shouldn't be needed in this case. I am just using it to make sure things run smoothly.

 

[Bukol]

Al,
done that already, works fine, now I just have the problem that after about 10 e-mails sent the memory of Outlook Exxpress seems to be full, so I have to empty the "Sent Items" window first before I acn run the next sequence.
I guess there is no solution for that?
Hans

 

[Bukol]

Al,
it started to work fine with bugs, but now it doesn't send anymore. It just piles up the Outlook Express "New Message" window and I have to manually click the send button.
Hans

 

[Al Chara]

The sent items question has been discussed on this board before. I don't remember the conclusion, but you can do a search for it.

The problem with it not sending has to do with this line: Application.SendKeys "%s"
This is telling your computer to hit ctrl+s, which is the shortcut to send an email. To get it working properly fool around with the two wait lines to make sure that the email is ontop, when you send the keys.

Does this make sense?

 

[Bukol]

Al,
actually the shortcut would be Alt+S, but it says in the help manual that the % has to be embedded in braces other then () - the curved ones which I cannot find on my keyboard and there is no way to insert symbols in the VB editor.
Hans

 

[Bukol]

Copied the braces from the help file of VB editor and played around with it, but it doesn't work.
{}
Help anybody!!
Hans

 

[Al Chara]

Actually I mean't Alt+S. The code I gave is correct in that way.

 

[keithkemble]

Bukol,

Sub SendMsg()
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{00062FFF-0000-0000-C000-000000000046}", 9, 0

Dim objOL As New Outlook.Application

Try using the ' in front of the dim objol line to green it out.

This is where the error applies.
I had exactly that today.

The preceding line (before Dim) references Outlook automatically with out using the libary in VBA menu.

When you use the Dim ObJol you require the linking to the library via the VBA reference libary noy though the auto linking precing.

 

[gareth]

Good 1 TOM