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!
LVL 6
hans_vdAsked:
Who is Participating?
 
jcoutureCommented:
I just finished writing a mass emailer for the company I work for.  I can't send you any of the code since my company owns that, but I will give you a few pointers.  (I don't want any points for this, it's just to help you on your way).

1. If it's a commercial app your writing, you might want to consider investing in a commercial email component such as EasyMail Objects.  We finally did that at work and it helped A LOT.  It allowed sending via Outlook, or SMTP direct.  I recommend the SMTP direct route so you don't have to hassle with Outlook security issues.

2. If sending emails to multiple people, be sure you put those addresses in the BCC list if you're concerned about giving your mailing list to each person.

3.  If creating HTML images that contain graphics, you'll need the images in the HTML document to be available either as attached files, hidden attached files (embedded) or stored on the web so the email can find it.  If the HTML email uses a IMG SRC = "C:\My Pictures\Company Logo.gif" tag then the recipient will get a whole lot of nothing since they probably don't have that image file on their hard drive.  The easiest way to include images is to either have them available on a web site, or attach them to the email and change the IMG SRC tag to read something like <IMG SRC="cid:logo.gif">  The cid tells the email to look in the attached files area of the email.  The picture will be listed in the attachments too, but it's a very simple solution.  Embedding the image in the email as a hidden attachment is quite a bit more involved.  You can find some code at http://www.outlookcode.com that talks about embedding images if you wish to persue that.

4.  When sending HTML email, you'll have to be sure that Word Wrap is turned OFF when sending the emails.  If the server is forcing a word wrap at the wrong spot, you'll end up with links that don't work or a break in the middle of a tag ect.  It's sort of sneaky and difficult to find that sort of problem since you'll only notice a problem when the word wrapping causes a bad HTML statement.

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.

Good luck.

JC.
0
 
edwardiiiCommented:
Hi, hans_vd.

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(olMailItem)
       
    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
0
 
Éric MoreauSenior .Net ConsultantCommented:
every time I need to send e-mail (plain or HTML) with VB6, I use the free vbSendMail component: http://www.freevbcode.com/ShowCode.Asp?ID=109
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
hans_vdAuthor Commented:
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?
0
 
Éric MoreauSenior .Net ConsultantCommented:
>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.
0
 
hans_vdAuthor Commented:
Yes, I understand that, but I'll have to send all the e-mails one by one then, won't I?
0
 
Éric MoreauSenior .Net ConsultantCommented:
sure you have unless all your customers are "mister Johnson" !
0
 
jcoutureCommented:
>> 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.
0
 
hans_vdAuthor Commented:
Ok, thanks to you all.
I needed a general idea on how to go about, and that's what you gave me!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.