Multiple email Recipients

[Gismo]

Hi all,

previously, i only had to email my report to 2 recipient
i now need to be able to email multiple recipients based on a query

below is what I had before
please advise how to email multiple recipients

 

[Gasman]

Not sure how you would do it in a macro, but in VBA you would concatenate the addresses in the To property dynamically as you have done there statically.

 

[Uncle Gizmo]

in VBA you would concatenate the addresses in the To property dynamically

See here:-

Display Field Data Horizontally - Nifty Access

Transform, Concatenate Examples A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done. Display Field...

www.niftyaccess.com

 

[Uncle Gizmo]

Gina Whipp has a nice blog on sending multiple emails from MS Access see here:-

Access Diva Send eMail to Multiple Recipients

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[Gismo]

Gina Whipp has a nice blog on sending multiple emails from MS Access see here:-

Access Diva Send eMail to Multiple Recipients

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

wow works great

any way to suppress the below message, and trigger allow?

 

[Gismo]

i am getting an error

Run-time Error '3061' Too Few Parameters. Expected 2​

Set rs = CurrentDb.OpenRecordset("SELECT * FROM CustomerDeclineemails ")

 

[Minty]

Does the query CustomerDeclineemails refer to any forms for criteria that aren't open or missing values?

 

[Gismo]

Does the query CustomerDeclineemails refer to any forms for criteria that aren't open or missing values?

no it does not refer to any forms
all data in the query seems fine when opened manually

 

[Gasman]

Post the sql please.

 

[Minty]

What's the SQL of the query?

 

[Gismo]

SELECT [DAW Sheet Data File].Registration, [DAW Sheet Data File].Sequence, [CSM/CSA].[e-mail] AS emailCSM, [CSM/CSA].Division, [CSM/CSA_1].[e-mail] AS emailCSA
FROM ([DAW Sheet Data File] INNER JOIN [CSM/CSA] ON [DAW Sheet Data File].CSM = [CSM/CSA].UserName) INNER JOIN [CSM/CSA] AS [CSM/CSA_1] ON [DAW Sheet Data File].CSA = [CSM/CSA_1].UserName
WHERE ((([DAW Sheet Data File].Registration)=[Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Registration]) AND (([DAW Sheet Data File].Sequence)=[Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Sequence]));

 

[Minty]

no it does not refer to any forms
all data in the query seems fine when opened manually

It does refer to forms - see the bits in Red below;

SELECT [DAW Sheet Data File].Registration, [DAW Sheet Data File].Sequence, [CSM/CSA].[e-mail] AS emailCSM, [CSM/CSA].Division, [CSM/CSA_1].[e-mail] AS emailCSA
FROM ([DAW Sheet Data File] INNER JOIN [CSM/CSA] ON [DAW Sheet Data File].CSM = [CSM/CSA].UserName) INNER JOIN [CSM/CSA] AS [CSM/CSA_1] ON [DAW Sheet Data File].CSA = [CSM/CSA_1].UserName
WHERE ((([DAW Sheet Data File].Registration)=[Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Registration]) AND (([DAW Sheet Data File].Sequence)=[Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Sequence]));

These are form references in the criteria.

 

[Gasman]

I ask you ?

 

[Gismo]

It does refer to forms - see the bits in Red below;

SELECT [DAW Sheet Data File].Registration, [DAW Sheet Data File].Sequence, [CSM/CSA].[e-mail] AS emailCSM, [CSM/CSA].Division, [CSM/CSA_1].[e-mail] AS emailCSA
FROM ([DAW Sheet Data File] INNER JOIN [CSM/CSA] ON [DAW Sheet Data File].CSM = [CSM/CSA].UserName) INNER JOIN [CSM/CSA] AS [CSM/CSA_1] ON [DAW Sheet Data File].CSA = [CSM/CSA_1].UserName
WHERE ((([DAW Sheet Data File].Registration)=[Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Registration]) AND (([DAW Sheet Data File].Sequence)=[Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Sequence]));

These are form references in the criteria.

sorry, misunderstood
it refers to the form it runs from, not a form with the same name
sorry for the confusion

 

[Gismo]

I ask you ?

sorry Sir, it was a misunderstanding from my side

 

[Minty]

Actually, I'm not sure that query makes sense, there is no comparison field for the second form criteria.

 

[Gasman]

See f theDBguy's function works for you.?

http://www.accessmvp.com/thedbguy/codes.php?title=generic

Linked from https://www.access-programmers.co.u...ew-parameters-expected-3.313393/#post-1715056

 

[Gismo]

Actually, I'm not sure that query makes sense, there is no comparison field for the second form criteria.

the query seems to works fine

 

[Minty]

the query seems to works fine
View attachment 88180

Sorry I misread the query first time around.

In your code can you debug.print [Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Registration] to make sure that subform is available to get the value from?

 

[Gismo]

Sorry I misread the query first time around.

In your code can you debug.print [Forms]![Menu]![StatusRegFilter].[Form]![DAWSheetStatus]![Registration] to make sure that subform is available to get the value from?

the form remains open all the time so it is available
not sure why it does this
if i hard code the registration and sequence, and remove the form reference, the error shifts to the recipient list

If Not IsNull(rs!emailCSM) Or Not IsNull(rs!emailCSA) Then
vRecipientList = vRecipientList & rs!emailCSM & rs!emailCSA & ";"