Solved

VB and mailing lists in Excel

Posted on 2002-05-23
5
157 Views
Last Modified: 2010-05-02
I have a database of club members in an excel spreadsheet.
Data includes names, address and e-mail address

Every 6 months I want to e-mail EACH member with THEIR own details (name, address, membership details etc.)

I have written VB macros to extract the relevant info for each member from their set of data and format into what would bethe contents of a message to be sent.

Q: How do I SEND this block of data to that person's e-mail address using VB in EXCEL.
I don't want to 'drag' the data into Outlook, I want to 'push' it from Excel.
How do I do it? CAN I do it?
I do not want to have to change to a new database (access for example) as this has taken a long time to set up and not everyone in the organisation has Access but we do all have excel so the person who takes this job over from me will just be able to run the appropriate macro.

Any suggestions?
0
Comment
Question by:GSD4ME
5 Comments
 
LVL 4

Accepted Solution

by:
Glowman earned 200 total points
ID: 7029730
You can use the Microsoft MAPI Messages Control and the Microsoft MAPI Session Control to add functionality to your Excel spreadsheets.  You can add the controls to a form by choosing Additional controls.  Then you can do something like this:

        MAPIMessage.SessionID = MAPISession.SessionID
        MAPIMessage.MsgIndex = -1
        MAPIMessage.Compose

Then you can loop through your list adding the users email to the Recipient Properties, like so:

        Do Until x = TotalEmails
            MAPIMessage.RecipIndex = x
            MAPIMessage.RecipAddress = Email(x)
            'change to 1 for TO
            'change to 2 for CC
            'change to 3 for BCC
            MAPIMessage.RecipType = 3
            MAPIMessage.ResolveName
            x = x + 1
        Loop

Then you can attach a spreadsheet or a message into the MsgSubject and MsgNoteText Properties.  And then call the Send Message like this:

        MAPIMessage.Send True

This will require that you have Outlook configured on the computer as well.
Hope this helps
G
0
 
LVL 4

Expert Comment

by:gencross
ID: 7029797
Here is the Macro you will need.  This will use Outlook to send the mail.  If you do not have Outlook installed it will not work.  You will also need to modify the cell number of the email address of course, but you should be able to see what it is doing very easily.  Just open the VB Editor in your XLS and paste this in, modify it, then call.  Hope this helps.

Sub Email()
   
    Dim objOL As Object
    Dim objEMail As Object
   
    Set objOL = CreateObject("Outlook.Application")
   
    For x = 1 To Sheet1.Rows.Count
       
        If Sheet1.Cells(x, 1) <> "" Then
            Set objEMail = objOL.CreateItem(olMailItem)
            With objEMail
                .Recipients.Add Sheet1.Cells(x, 1) 'Cell of the email address
                .Subject = "Your details "
                .Body = "Details"
                'To send an attachment
                '.Attachments.Add ("C:\my documents\file.txt")
                .Send
            End With
            Set objEMail = Nothing
        End If
       
    Next
   
End Sub
0
 
LVL 4

Expert Comment

by:gencross
ID: 7029993
You may also want to add an Exit For when the last row of data is hit then it will not continue to go through all rows.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7030081
You could use this excellent free tool:
http://www.freevbcode.com/ShowCode.Asp?ID=109
0
 

Author Comment

by:GSD4ME
ID: 7268811
Many thanks - you were the closest to what I needed
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now