SendObject Action and how to use it in a Macro

[jool]

Hello all,

I have created a button called 'Notify person' on an Access form. The purpose of this button is to notify a member of staff when an entry into a table has been made against his / her name.

I have attempted to do this as follows.

In the properties tab of my button, on the event tab, at the On click option
I have created a Macro.

I have selected the SendObject command.

I wish to send the email to the address of the person input into
a field on my table, entitled 'email'. As such in the
'to' column of the action arguments (in the macro) I have entered

This however, does not work and I receive the message (unknown message
recipient, the message was not sent).

I guess I'm just not specifying the field properly, but essentially I want
to send the email to whatever address is specified in the 'email' field.

Many thanks in advance.

Adam

 

[shuff]

Response to: SendObject Action in Macro

Adam,

Precede your relative pointer on the TO: line with an equal sign, like this:

=[Forms]![frmProposeMgr]![CollectorMail]

That will cause the SendObject statement to EVALUATE the pointer, not take it literally as being an email address.

By the way, you can also force text/and or field data in the Subject line and even the body of the email. For example, I have an application that places the following in the subject line (literal text combined with someone's name obtained from field data):

="Request For Override APPROVED: NPV Proposal#" & [Forms]![frmProposeMgr]![Reference] & "/" & [Forms]![frmProposeMgr]![DebtorLastName]

This results in a subject line like this:

Request For Override APPROVED: NPV Proposal#34550/Jones

That's more than you asked about, but the SendObject command is one of my favorites because it is so configurable. Hope this helps!

SLH

 

[kentwood]

I am trying to do exactly what SLH has described for the subject line. I used the same format that SLH did, but it is not working for me and I get an error message when I run the macro that says:

Microsoft Office Access can't parse the expression:""="Work Order Status/Completion Details: Work Order #"&
[forms]![frmWrkOrder].[OrderID] & ”/” &
[forms]![frmWrkOrder].[ProviderName] & “/” &
[forms]![frmWrkOrder].[WorkOrderName]

It is set up as SendObject, and also attaches a report in Snapshot format.

Any idea what is wrong. This would be so cool if I could get it to work.

 

[manix]

Adam,

Precede your relative pointer on the TO: line with an equal sign, like this:

=[Forms]![frmProposeMgr]![CollectorMail]

That will cause the SendObject statement to EVALUATE the pointer, not take it literally as being an email address.

By the way, you can also force text/and or field data in the Subject line and even the body of the email. For example, I have an application that places the following in the subject line (literal text combined with someone's name obtained from field data):

="Request For Override APPROVED: NPV Proposal#" & [Forms]![frmProposeMgr]![Reference] & "/" & [Forms]![frmProposeMgr]![DebtorLastName]

This results in a subject line like this:

Request For Override APPROVED: NPV Proposal#34550/Jones

That's more than you asked about, but the SendObject command is one of my favorites because it is so configurable. Hope this helps!

SLH

This is an excellent bit of information on the sendobject macro. THANK YOU!

I have used this to full effect, enabling me to send out notifications of a concern that has been raised to a select group of people as stated on my form!!!

I used the following to send the notification to more than one person:

=[Forms]![frm_concern]![Copy To] & ";" & [Forms]![frm_concern]![Copy To2] & ";" & [Forms]![frm_concern]![Copy To3] & ";" & [Forms]![frm_concern]![Copy To4]

This reads 4 fields each containing email addresses and inserts the require semi colon in between each. However, it works fine when 4 email addresses are added, but what if just one is used. The returned string is john@hotmail.com;;; and then Access states the error "Unknown receipient(s), message was not sent." Can anyone confirm if there is a way around this? Or do I have to use another method?

 

[Rabbie]

You need to check if the different copy_to fields are populated and skip the empty ones and the trailing ; in that case

 

[manix]

You need to check if the different copy_to fields are populated and skip the empty ones and the trailing ; in that case

Cheers Rabbie but I am not sure how to do that!

 

[Rabbie]

I would suggest changing it to match whats below

=[Forms]![frm_concern]![Copy To] & iif(len([Forms]![frm_concern]![Copy To2]> 0), ";" & [Forms]![frm_concern]![Copy To2] ," ") & iif(len([Forms]![frm_concern]![Copy To3]> 0), ";" & [Forms]![frm_concern]![Copy To3] ," ") & iif(len([Forms]![frm_concern]![Copy To4]> 0), ";" & [Forms]![frm_concern]![Copy To4] "," ")

You may needto correct any typos

HTH

 

[manix]

I would suggest changing it to match whats below

=[Forms]![frm_concern]![Copy To] & iif(len([Forms]![frm_concern]![Copy To2]> 0), ";" & [Forms]![frm_concern]![Copy To2] ," ") & iif(len([Forms]![frm_concern]![Copy To3]> 0), ";" & [Forms]![frm_concern]![Copy To3] ," ") & iif(len([Forms]![frm_concern]![Copy To4]> 0), ";" & [Forms]![frm_concern]![Copy To4] "," ")

You may needto correct any typos

HTH

Excellent work Rabbie, but unfortuantely it is too long for the Macro field! It may work if I reduce the amount of people for it to be sent to. I have done this and reduced the amount in the "To" field to three. The fourth can just appear in the CC field, it's not a problem.

Thanks for your help!

 

[manix]

Excellent work Rabbie, but unfortuantely it is too long for the Macro field! It may work if I reduce the amount of people for it to be sent to. I have done this and reduced the amount in the "To" field to three. The fourth can just appear in the CC field, it's not a problem.

Thanks for your help!

Actaully I spoke too soon! There is a type mistmatch error with that code! Will have to try and see if all the formatting is correct!

 

[Rabbie]

Hi

I have just tested this simplified version of that string and it works fine assuming that all the input boxes are text.

=[copy1] & IIf(Len([copy2])>0,";" & [copy2]," ") & IIf(Len([copy3])>0,";" & [copy3]," ")

You will need to expand the names to match your data.

 

[manix]

Hi

I have just tested this simplified version of that string and it works fine assuming that all the input boxes are text.

=[copy1] & IIf(Len([copy2])>0,";" & [copy2]," ") & IIf(Len([copy3])>0,";" & [copy3]," ")

You will need to expand the names to match your data.

Well done Rabbie, great stuff. Works perfectly this time. I did think there was a need to adjust the brackets around the field criteria, but I could not figure it to work properly.

At last the code for entering up to 3 email addresses from 3 fileds in a specific form is:

=[Forms]![frm_concern]![Copy To] & IIf(Len([Forms]![frm_concern]![Copy To2])>0,";" & [Forms]![frm_concern]![Copy To2]," ") & IIf(Len([Forms]![frm_concern]![Copy To3])>0,";" & [Forms]![frm_concern]![Copy To3]," ")

This allows you to make the macro look at 3 fields in your table and grab email addresses, as long as the fields are text fields. It will ignore any fields that don't have an address and not insert the ;.

Hope that helps others as well. Thanks goes to Rabbie for some great help