Automated Email Address

[Drenda]

An extension to my original question which I think I have almost resolved. I have the following VB Code to email a report in snapshot format and it currently works

DoCmd.SendObject acSendReport, "Cash Flow Approval for ALL", cFormatSNP, "John.Smith@emailserver.com", , , "Goods Have Been Delivered", "Goods Have Been Delivered Regarding Cost Authorisation Shown on the Attached", , False

I would like to automate the email address so it uses the value of a control on a form so for example, if control A on Form 1 = "Fred.Smith@emailserver.com" then the code would read something like

DoCmd.SendObject acSendReport, "Cash Flow Approval for ALL", cFormatSNP, Forms![Form 1].[control A], , , "Goods Have Been Delivered", "Goods Have Been Delivered Regarding Cost Authorisation Shown on the Attached", , False

I cannot seem to get this to work and I assume this has to be achieved in another way.

Again, any help would be appreciated

 

[Remou]

It should work, assuming that Form 1 is open. Have you tried stepping through the code to chech the value of Control A? Is Control A a hyperlink field? If so, you will need to strip out the address.

 

[Drenda]

Thanks

I have tried this and the form is open, perhaps I am making a mistake with syntax or something

DoCmd.SendObject acSendReport, "Cash Flow Approval for ALL", acFormatSNP, Forms![Email Address]!, , , "Goods Have Been Delivered", "Goods Have Been Delivered Regarding Cost Authorisation Shown on the Attached", , False

where the email address = Forms![Email Address]![Email]

I get a message saying an expression you entered is the wron data type for one of the arguments

 

[Remou]

Is Control A a hyperlink field? If so, you will need to strip out the address.

 

[Drenda]

No, Control A (Forms![Email Address[! is an unbound text box on a form. To test this, I have simply typed in

="John.Smith@emailserver.com"

in the control source of the text box and referred to it from the code with the form open

 

[Remou]

I think you may find that the textbox has not been updated and so the value is null. You could move to a different control or choose Records->Save Record from the menubar and try again. If you put:

[tt]MsgBox Forms![Email Address[! & ""[/tt]

Just before the DoCmd line, it will show you the contents of the textbox as seen by the programme.

 

[Drenda]

Very strage, it now seems to work, a final question. If I want to refer to two email adresses from different controls from a form, say and [Email2] would it go something like this

DoCmd.SendObject acSendReport, "Cash Flow Approval for ALL", acFormatSNP, Forms![Email Address]![Email];Forms![Email Address]![Email2], , , "Goods Have Been Delivered", "Goods Have Been Delivered Regarding Cost Authorisation Shown on the Attached", , False

 

[JerryKlmns]

Maybe

DoCmd.SendObject acSendReport, "Cash Flow Approval for ALL", acFormatSNP, Forms![Email Address]! & ";" & Forms![Email Address]![Email2] , , , "Goods Have Been Delivered", "Goods Have Been Delivered Regarding Cost Authorisation Shown on the Attached", , False

 

[Drenda]

Thanks -

I'll give it a go and let you know how I get on

 

[Drenda]

Thanks - ths was brilliant and has saved me hours of trying to work this out. One last question (if this is okay)
Although I have a further question

I have at put the Docmd. SendObject as an event procedure on the after update of a yes / no check box on my form but only want to send the object if the check box is yes. I have tried to put an if statement before the Docmd.SendObject but this does not seem to work

Thanks
PLEASE NOTE I HAVE POSTED THIS ON A PREVIOUS REQUEST FOR HELP SO HOPE THIS DOES NOT CONFUSE MATTERS

 

[Remou]

You need something on the lines of:

Sub chkTick_OnClick()
If Nz(chkTick,False)=True Then
   DoCmd....
Else
   'What ever
End if

 

[Drenda]

Worked a trest, Many thanks