Sending Mail

Smudger Smith · Jul 21, 2009

First off, I have tried to find an answer but with no luck.

I want my Access DB (2002) to send via e-mail (Outlook) to a number of addresses. The mail will send to the LAST person on the list but not to all people.

Help would be really appreciated:

'------------------------------------------------------------
' To Send an e-mail from Access to a list of users
'------------------------------------------------------------
Function SendE_Mail_Tbl_1120()

Set dbs = CurrentDb
Set rstEmailDets = dbs.OpenRecordset("SELECT * FROM Tbl_1120_New_Position_And_KID_Mail;")
strEmail = Nz(rstEmailDets("Requester_E-Mail_Address"), ";")

On Error GoTo Macro1_Err
DoCmd.SendObject acTable, "Tbl_0040_OM_Team", "MicrosoftExcelBiff8(*.xls)", strEmail, "", "", "New Test (Subject)", "New Test (Body)", False, ""

Macro1_Exit:
Exit Function
Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

KenHigg · Jul 21, 2009

What does strEmail look like when you step trough the code? You may not be getting a semicolon (

seperating the names - ?

Smudger Smith · Jul 21, 2009

How do i do that then?

KenHigg · Jul 21, 2009

That depends. It's not apparent to me how you are looping through the recordset.

Smudger Smith · Jul 21, 2009

I think you have hit the nail on the head there. What you see is what you get on the module front. I am very 'OLD SKOOL' and mainly use Queries and Macros, only venturing into VBA when i really have to.

So...

How do i loop and how do i get it to start from the top and work down the list?

Thanks again

KenHigg · Jul 21, 2009

Hum... Let's back up one step. You have accounted for nulls with the nz() function. Can you eliminate nulls before hand via the 'Select' statement? This will make the loop/concatenate thing a bit less complicated.

Smudger Smith · Jul 21, 2009

Hiya,

There are no nulls in there as they are taken out when the table is created. Does that help?

KenHigg · Jul 21, 2009

Yes, that helps - My internet is real slow for some reason... Let me tinker with it for a second.

Smudger Smith · Jul 21, 2009

thank you so much

KenHigg · Jul 21, 2009

Something like:

Code:

Function SendE_Mail_Tbl_1120()

Set dbs = CurrentDb

Set rstEmailDets = dbs.OpenRecordset("SELECT * FROM Tbl_1120_New_Position_And_KID_Mail;")

rstEmailDets.MoveFirst

Do Until rstEmailDets.EOF

    strEmail = strEmail & rstEmailDets("Requester_E-Mail_Address") & ";"
    
    rstEmailDets.MoveNext

Loop

DoCmd.SendObject acTable, "Tbl_0040_OM_Team", "MicrosoftExcelBiff8(*.xls)", strEmail, "", "", "New Test (Subject)", "New Test (Body)", False, ""

dbs.Close
rstEmailDets.Close

End Function

(I'm not sure what all of that error stuff was doing...)

Smudger Smith · Jul 21, 2009

Cheers Ken - I really appreciate your help on this.

Thank you so much

KenHigg · Jul 21, 2009

Glad to help

Sending Mail

Smudger Smith

I like numbers me

KenHigg

Registered User

Smudger Smith

I like numbers me

KenHigg

Registered User

Smudger Smith

I like numbers me

KenHigg

Registered User

Smudger Smith

I like numbers me

KenHigg

Registered User

Smudger Smith

I like numbers me

KenHigg

Registered User

Smudger Smith

I like numbers me

KenHigg

Registered User

Similar threads

Users who are viewing this thread