?
Solved

e-mailing program

Posted on 2005-04-25
9
Medium Priority
?
198 Views
Last Modified: 2010-05-02
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!
0
Comment
Question by:hans_vd
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:edwardiii
ID: 13860562
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
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 13863313
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
 
LVL 3

Accepted Solution

by:
jcouture earned 1600 total points
ID: 13863341
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Author Comment

by:hans_vd
ID: 13864689
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 13865331
>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
 
LVL 6

Author Comment

by:hans_vd
ID: 13865399
Yes, I understand that, but I'll have to send all the e-mails one by one then, won't I?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 13865811
sure you have unless all your customers are "mister Johnson" !
0
 
LVL 3

Expert Comment

by:jcouture
ID: 13866688
>> 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
 
LVL 6

Author Comment

by:hans_vd
ID: 13867560
Ok, thanks to you all.
I needed a general idea on how to go about, and that's what you gave me!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question