Email to a Distribution List in Access (1 Viewer)

[wsaccess]

Hi,

I have a Distribution list in my Outlook Contacts called "Test"

Is there a way I can send email to this contact group?
Currently, what I am doing is this:

Dim strMailList as string

strMailList = "email1;email2"

.To = strMailList

What I want to do is instead of strMailList, I would like to use "Test".

Is it possible?

 

[GohDiamond]

Checkout this page : http://www.jephens.com/2007/05/13/how-to-send-e-mail-from-ms-access-using-outlook/

change MyMail.To = MailList("email") to MyMail.To = MailList("Test")

Cheers
Goh

 

[wsaccess]

Hi,

this means that I need to store the emails in a table, right?
Is there a way of getting it directly from the outlook?

 

[Minty]

"Test@yourdomain" is treated as a valid email address so why not just send to that ?
Or have I missed the point (quite likely)

 

[wsaccess]

Thanks to both the replies.

I guess I am totally confused now....
I followed the link that GohDiamond sent, and in it the "MailList" is a record set from a table with all the emai l Ids.
My doubt was if there is a way of not using tables, and use the Distribution List directly from the outlook?

Or, am I meant to be doing like this:

1. Create a table to store the email Ids
2. Create a Record set to get the email Ids from Outlook Distribution list
3. Loop through the record set to send email

I didn't understand clearly how I should be doing it.. Can somebody explain to me clearly please?

 

[Minty]

If you send a email to test@yourdomain does it get distributed to the correct respondents?

If it does then don't try and get the list of email addresses out of outlook, just send your email to test@yourdomain.

 

[GohDiamond]

Yes, I like Minty's suggestion: create a distribution group in Outlook from the contacts there, no need to import them into access and send the mail to that distribution group.
You can edit your distribution members list in Outlook at any time.
MyMail.To = "MyDistributionList@MyDomain.com"

Cheers
Goh

 

[wsaccess]

it didn't work!
I have a distribution list named 'Test' in my outlook.
In my code I used the following:

Dim OL As Outlook.Application
Dim MyMail As Outlook.MailItem

Set OL = Outlook.Application
Set MyMail = OL.CreateItem(olMailItem)

With MyMail
.To = "Test@MyDomain.com"
.Subject = "Test"
.Send
End With

It came with message failure error

Any clue?

 

[Minty]

What was the message failure?

What happens if you change .Send to .Display ?

 

[GinaWhipp]

On the *.To* line just put...

"Test"

You do not need the Domain name.

 

[wsaccess]

When I code:

.To = "Test"

I am getting the following error message:

"Access does not recognise on or more names"

and it stops at .Send

It does not send the email.

 

[GinaWhipp]

Hmm, something amiss, try...

    Dim OutApp As Object
    Dim OutMail As Object
   
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
    On Error Resume Next
    With OutMail
         .To = "Test"
         .Subject = "This is the Subject line"
         .Body = "This is the Body"
         .Send
    End With
    
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

 

[wsaccess]

I tried the code exactly as you said.
Funny enough, I don't get any error message, but it does not send the email either!
I checked my sent items, inbox.

What could be happening?

 

[GinaWhipp]

Well, since I know that works let's do this...

1. What version of Access?
2. What version of Outlook?
3. Is Outlook open?
4. What version of Windows?
5. What bitness of Office are you using 32 bit or 64 bit?

 

[wsaccess]

Version of Access - Access 2013
Version of Outlook - Outlook 2013
Outlook is Open
Version of Windows - Windows 7 Professional
Operating Sytem - 64 bit

 

[GinaWhipp]

Is Office 32 bit?

And what happens if you change .Send to .Display? Does the eMail open up?

 

[wsaccess]

Yes, it opens up.
In the 'To; field, it has the word Test.
Not the emails in the Test Distribution list.
How can I find out what bit of Microsoft Office I am using?

 

[Grumm]

Well in the To field you have Test because this is your code :

    Dim OutApp As Object
    Dim OutMail As Object
   
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
    On Error Resume Next
    With OutMail
         .To = "Test"  <==== YOU NEED A VALID EMAIL HERE
         .Subject = "This is the Subject line"
         .Body = "This is the Body"
         .Send
    End With
    
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

You still need to replace "Test" with your actual email list. (And all emails should be separated with ; if you add multiple ones.

 

[wsaccess]

Hi Grumm,

I was doing as you said. Since I have a huge distribution list, which keeps changing often, I wanted to try the option of using the Distribution List directly in the code, so that I wont have to edit the code each time when the list changes.

Thanks

 

[GinaWhipp]

Okay, so if you click Test you should see your Distribution List. We now just need o figure out why it's not sending.

When you open Word or Excel you should see what version. Actually, you should also see this when you open Access but it might be opening too fast.