hans_vd
asked on
e-mailing program
Hi experts,
I need - or at least get to know how - to write an application that can send html formatted e-mails to a large group of people based on a list of e-mail adresses that exists in MS Excel.
Does any of you have any experience with writing such an application? I know about reading Excel files from VB, but I don't know much about how to automate the e-mail to a large group of e-mail addresses (+/- 5000 addresses in the list) and how to create HTML e-mails.
The one that can help me out best by giving me a good idea on how to work things out, gets the points!
I need - or at least get to know how - to write an application that can send html formatted e-mails to a large group of people based on a list of e-mail adresses that exists in MS Excel.
Does any of you have any experience with writing such an application? I know about reading Excel files from VB, but I don't know much about how to automate the e-mail to a large group of e-mail addresses (+/- 5000 addresses in the list) and how to create HTML e-mails.
The one that can help me out best by giving me a good idea on how to work things out, gets the points!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your comments are very usefull!
I'll check out the free mail component emoreau suggested.
>> When sending HTML email, you'll have to be sure that Word Wrap is turned OFF when sending the emails
Where is it that Word Wrap comes up? In Outlook? And how to turn it off? Can I do that in my programming code, or is it a setting?
>> Not a lot of help for you, but hopefully it will give you an idea of some of the tasks you'll need to undertake.
To get an idea was the reason for this thread, so it ís a lot of help for me!!!
>> be sure you put those addresses in the BCC list
I'll make sure to do that. Can you tell me from your experience how many e-mail addresses I can put there at one time? eg I have to send 5000 e-mails, and I can put 50 in BCC a time and I'll have to send 100 e-mails, am I right?
Ai, I'm just thinking of something: If I do it like that, it is impossible to add a personal message (style: "dear mister Johnson" - I take the name from my excel file) I suppose? Anybody having experience with that?
I'll check out the free mail component emoreau suggested.
>> When sending HTML email, you'll have to be sure that Word Wrap is turned OFF when sending the emails
Where is it that Word Wrap comes up? In Outlook? And how to turn it off? Can I do that in my programming code, or is it a setting?
>> Not a lot of help for you, but hopefully it will give you an idea of some of the tasks you'll need to undertake.
To get an idea was the reason for this thread, so it ís a lot of help for me!!!
>> be sure you put those addresses in the BCC list
I'll make sure to do that. Can you tell me from your experience how many e-mail addresses I can put there at one time? eg I have to send 5000 e-mails, and I can put 50 in BCC a time and I'll have to send 100 e-mails, am I right?
Ai, I'm just thinking of something: If I do it like that, it is impossible to add a personal message (style: "dear mister Johnson" - I take the name from my excel file) I suppose? Anybody having experience with that?
>to add a personal message (style: "dear mister Johnson" -
The component I linked you too only sends a message you created. Create it as you want and set the body property to the HTML content variable you created programmatically and you are set.
The component I linked you too only sends a message you created. Create it as you want and set the body property to the HTML content variable you created programmatically and you are set.
ASKER
Yes, I understand that, but I'll have to send all the e-mails one by one then, won't I?
sure you have unless all your customers are "mister Johnson" !
>> I'll check out the free mail component emoreau suggested.
Good Idea. It's a good component and I've used it for various SMTP tools.
>>Where is it that Word Wrap comes up? In Outlook? And how to turn it off? Can I do that in my programming code, or is it a setting?
Word Wrap should be turned off using code. If you're sending email via Outlook (MAPI), then you'll probably have to use code to set the outlook object property for wordwrap. In SMTP, it's also a property of the server that you'll need to turn off. I'm sure you've seen places in email apps that have a property of "Wrap text at 72 character" or something like that. It's ok for text emails, but potentially bad for HTML emails. You'll have to dig into the properties for your SMTP server to figure out the specifics for turning off Word Wrap for that server. It's probably a good idea to store the initial settings when starting the app, adjust the settings via code, send your emails, then restore the settings to their initial state. You don't want your app to completely screw up the email settings for other people using the mail server. If you do this, then they can't blame you for bad email issues.
>>Can you tell me from your experience how many e-mail addresses I can put there at one time? eg I have to send 5000 e-mails, and I can put 50 in BCC a time and I'll have to send 100 e-mails, am I right?
This might be a moot point if you are going to customize the text to the specific sender (Dear Mr. Johnson). If that's the case, then you'll need to send out individual emails and then you'll just put the address in the normal "To" area, not the BCC area.
If you do send out to multiple people at once, then perhaps you'll want to do so in smaller chunks like you said (10 to 50 address at a time). Remember, if something goes bad with the email, like a malformed HTML document or a hickup in the email server, you are going to be sending that problem document to everyone on the list. In my opinion, smaller groups are better to minimize the number of people you potentially upset by a corrupt email.
>> is impossible to add a personal message (style: "dear mister Johnson" - I take the name from my excel file) I suppose? Anybody having experience with that?
You'll need to create a base line email with some sort of token that indicates where you are going to replace text. If your email looks something like " Dear xxxFirstNameHerexxx, " then you would do a replace on the subject text and body text for your token "xxxFirstNameHerexxx" and put in the actual first name of the recipient. This would definitely require one email per address.
Good Idea. It's a good component and I've used it for various SMTP tools.
>>Where is it that Word Wrap comes up? In Outlook? And how to turn it off? Can I do that in my programming code, or is it a setting?
Word Wrap should be turned off using code. If you're sending email via Outlook (MAPI), then you'll probably have to use code to set the outlook object property for wordwrap. In SMTP, it's also a property of the server that you'll need to turn off. I'm sure you've seen places in email apps that have a property of "Wrap text at 72 character" or something like that. It's ok for text emails, but potentially bad for HTML emails. You'll have to dig into the properties for your SMTP server to figure out the specifics for turning off Word Wrap for that server. It's probably a good idea to store the initial settings when starting the app, adjust the settings via code, send your emails, then restore the settings to their initial state. You don't want your app to completely screw up the email settings for other people using the mail server. If you do this, then they can't blame you for bad email issues.
>>Can you tell me from your experience how many e-mail addresses I can put there at one time? eg I have to send 5000 e-mails, and I can put 50 in BCC a time and I'll have to send 100 e-mails, am I right?
This might be a moot point if you are going to customize the text to the specific sender (Dear Mr. Johnson). If that's the case, then you'll need to send out individual emails and then you'll just put the address in the normal "To" area, not the BCC area.
If you do send out to multiple people at once, then perhaps you'll want to do so in smaller chunks like you said (10 to 50 address at a time). Remember, if something goes bad with the email, like a malformed HTML document or a hickup in the email server, you are going to be sending that problem document to everyone on the list. In my opinion, smaller groups are better to minimize the number of people you potentially upset by a corrupt email.
>> is impossible to add a personal message (style: "dear mister Johnson" - I take the name from my excel file) I suppose? Anybody having experience with that?
You'll need to create a base line email with some sort of token that indicates where you are going to replace text. If your email looks something like " Dear xxxFirstNameHerexxx, " then you would do a replace on the subject text and body text for your token "xxxFirstNameHerexxx" and put in the actual first name of the recipient. This would definitely require one email per address.
ASKER
Ok, thanks to you all.
I needed a general idea on how to go about, and that's what you gave me!
I needed a general idea on how to go about, and that's what you gave me!
I've not experimented with the maximum recipients one can email at the same time, but if your emails are identical, you can use something like the following to create an email which supports HTML-formatting. This routine will grab the 100 email addresses housed in current worksheet cells A1 through A100. Just make sure to add a reference to your Outlook Object library (and your Excel Object library if you're not doing this in Excel VBA):
Dim outApp As Outlook.Application
Dim olMail As MailItem
Dim strFontControlBody, strSubject, strTemplate, strTo As String
Dim x As Integer
Set outApp = New Outlook.Application
Set olMail = outApp.CreateItem(olMailIt
strFontControlBody = "<font face = MS Sans Serif size=3>" & "Your body text here" & "</font>"
strSubject = "Your subject here"
For x = 1 To 100
strTo = LTrim(strTo) & Cells(x, 1) & ";"
Next x
With olMail
.To = strTo
.Subject = strSubject
.HTMLBody = strFontControlBody
'.Attachments.Add _
'source:="C:\Howdy.jpg"
.Display
End With