Sending Mail (1 Viewer)

S

Smudger Smith

I like numbers me

Local time

Today, 04:53

Joined

May 30, 2007

Messages

25

• Jul 21, 2009

• #1

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

Registered User

Local time

Yesterday, 23:53

Joined

Jun 9, 2004

Messages

13,327

• Jul 21, 2009

• #2

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

 

S

Smudger Smith

I like numbers me

Local time

Today, 04:53

Joined

May 30, 2007

Messages

25

• Jul 21, 2009

• #3

How do i do that then?

 

KenHigg

Registered User

Local time

Yesterday, 23:53

Joined

Jun 9, 2004

Messages

13,327

• Jul 21, 2009

• #4

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

 

S

Smudger Smith

I like numbers me

Local time

Today, 04:53

Joined

May 30, 2007

Messages

25

• Jul 21, 2009

• #5

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

Registered User

Local time

Yesterday, 23:53

Joined

Jun 9, 2004

Messages

13,327

• Jul 21, 2009

• #6

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.

 

S

Smudger Smith

I like numbers me

Local time

Today, 04:53

Joined

May 30, 2007

Messages

25

• Jul 21, 2009

• #7

Hiya,

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

 

KenHigg

Registered User

Local time

Yesterday, 23:53

Joined

Jun 9, 2004

Messages

13,327

• Jul 21, 2009

• #8

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

 

S

Smudger Smith

I like numbers me

Local time

Today, 04:53

Joined

May 30, 2007

Messages

25

• Jul 21, 2009

• #9

thank you so much

 

KenHigg

Registered User

Local time

Yesterday, 23:53

Joined

Jun 9, 2004

Messages

13,327

• Jul 21, 2009

• #10

Something like:

Code:Copy to clipboard

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...)

 

S

Smudger Smith

I like numbers me

Local time

Today, 04:53

Joined

May 30, 2007

Messages

25

• Jul 21, 2009

• #11

Cheers Ken - I really appreciate your help on this.

Thank you so much

 

KenHigg

Registered User

Local time

Yesterday, 23:53

Joined

Jun 9, 2004

Messages

13,327

• Jul 21, 2009

• #12

Glad to help