Docmd Send Object - multiple CC fields from a form

  • Thread starter Thread starter Paul Worsnop
  • Start date Start date
P

Paul Worsnop

Hi,

I am in the process of developing a form which automatically emails people a
report.

I have it working and attaching the relevant report. However there is one
area that is not working as I would like it to.

The form has 3 fields as potentially the report could need to be emailed to
3 different people at a particular organisation. These are called
suppleremail, supplieremailtwo and supplieremailthree on the form.

I have attempted to add each of these fields, separated in the code with a
semi colon as follows:

If PRHolTable![PR Hol In] = True Then
If Me![supplier auto email] = True Then
DoCmd.SendObject acSendReport, "Requisition Report",
acFormatRTF, Me![SupplierEmail] ; Me![supplieremailtwo] ;
Me![supplieremailthree], , _
"Organisation Name Requisition - " & RacSectSubTable![RSS
Requisition No], "Please find attached automatically generated Purchase Order
- in the event of any queries please do not hesitate to contact me." _
& vbCrLf & vbCrLf & "Thankyou" & vbCrLf & vbCrLf & _
"Best Regards" & vbCrLf & "Person Name" & vbCrLf & "Person Job
Title" & vbCrLf & "Company Name" & vbCrLf & "Company Address" _
& vbCrLf & "Company Town" & vbCrLf & "Company Post Code" &
vbCrLf & vbCrLf & "Tel Number" & vbCrLf & "Fax Number" & vbCrLf & _
"Email: (e-mail address removed)", False
MsgBox "Email successfully sent"
End If

I get a syntax error though and cant work out what syntax I need to use?!

Can anyone help?

Thanks in advance.
 
You need to create a semi-colon delimited string:

Me![SupplierEmail] & ";" & Me![supplieremailtwo] & ";" &
Me![supplieremailthree]
 
Thanks Douglas thats worked superbly!

There is one other issue which I also need to address.

This works well providing that a CC email address is present. However, if it
is the case that there is no CC email address to send to how do I work around
so the code works? At the moment this falls over and displays a

"Unknown Message Recipient - The message was not sent"

Thanks

Douglas J. Steele said:
You need to create a semi-colon delimited string:

Me![SupplierEmail] & ";" & Me![supplieremailtwo] & ";" &
Me![supplieremailthree]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul Worsnop said:
Hi,

I am in the process of developing a form which automatically emails people
a
report.

I have it working and attaching the relevant report. However there is one
area that is not working as I would like it to.

The form has 3 fields as potentially the report could need to be emailed
to
3 different people at a particular organisation. These are called
suppleremail, supplieremailtwo and supplieremailthree on the form.

I have attempted to add each of these fields, separated in the code with a
semi colon as follows:

If PRHolTable![PR Hol In] = True Then
If Me![supplier auto email] = True Then
DoCmd.SendObject acSendReport, "Requisition Report",
acFormatRTF, Me![SupplierEmail] ; Me![supplieremailtwo] ;
Me![supplieremailthree], , _
"Organisation Name Requisition - " & RacSectSubTable![RSS
Requisition No], "Please find attached automatically generated Purchase
Order
- in the event of any queries please do not hesitate to contact me." _
& vbCrLf & vbCrLf & "Thankyou" & vbCrLf & vbCrLf & _
"Best Regards" & vbCrLf & "Person Name" & vbCrLf & "Person Job
Title" & vbCrLf & "Company Name" & vbCrLf & "Company Address" _
& vbCrLf & "Company Town" & vbCrLf & "Company Post Code" &
vbCrLf & vbCrLf & "Tel Number" & vbCrLf & "Fax Number" & vbCrLf & _
"Email: (e-mail address removed)", False
MsgBox "Email successfully sent"
End If

I get a syntax error though and cant work out what syntax I need to use?!

Can anyone help?

Thanks in advance.
Click to expand...
Click to expand...
 
One approach that should work takes advantage of the fact that +, when used
as a text concatenation character, propagates Nulls:

Me![SupplierEmail] & (";" + Me![supplieremailtwo]) & (";" +
Me![supplieremailthree])

If that doesn't work, you'll have to add functionality to your code to
determine whether or not the field has a value in it before concatenating it.

--
Doug Steele, Microsoft Access MVP



Paul Worsnop said:
Thanks Douglas thats worked superbly!

There is one other issue which I also need to address.

This works well providing that a CC email address is present. However, if it
is the case that there is no CC email address to send to how do I work around
so the code works? At the moment this falls over and displays a

"Unknown Message Recipient - The message was not sent"

Thanks

Douglas J. Steele said:
You need to create a semi-colon delimited string:

Me![SupplierEmail] & ";" & Me![supplieremailtwo] & ";" &
Me![supplieremailthree]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul Worsnop said:
Hi,

I am in the process of developing a form which automatically emails people
a
report.

I have it working and attaching the relevant report. However there is one
area that is not working as I would like it to.

The form has 3 fields as potentially the report could need to be emailed
to
3 different people at a particular organisation. These are called
suppleremail, supplieremailtwo and supplieremailthree on the form.

I have attempted to add each of these fields, separated in the code with a
semi colon as follows:

If PRHolTable![PR Hol In] = True Then
If Me![supplier auto email] = True Then
DoCmd.SendObject acSendReport, "Requisition Report",
acFormatRTF, Me![SupplierEmail] ; Me![supplieremailtwo] ;
Me![supplieremailthree], , _
"Organisation Name Requisition - " & RacSectSubTable![RSS
Requisition No], "Please find attached automatically generated Purchase
Order
- in the event of any queries please do not hesitate to contact me." _
& vbCrLf & vbCrLf & "Thankyou" & vbCrLf & vbCrLf & _
"Best Regards" & vbCrLf & "Person Name" & vbCrLf & "Person Job
Title" & vbCrLf & "Company Name" & vbCrLf & "Company Address" _
& vbCrLf & "Company Town" & vbCrLf & "Company Post Code" &
vbCrLf & vbCrLf & "Tel Number" & vbCrLf & "Fax Number" & vbCrLf & _
"Email: (e-mail address removed)", False
MsgBox "Email successfully sent"
End If

I get a syntax error though and cant work out what syntax I need to use?!

Can anyone help?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top