Copy and paste. The final piece of the jigsaw! (1 Viewer)

Dan25e · Dec 3, 2014

Hi,

I'm in the process of producing electronic versions of several paper based processes for the engineering company I work for. I've set up electronic versions of the relevant forms, added auditing code, buttons that send reports via email etc that all work a treat

. BUT!

The final piece of the jigsaw is traceability of each email. What I want to be able to do when I click the send email button on my form I want the ID of the record the form is based on to paste itself into the subject line of the email.

I've had a google around either ended up crashing my database

or getting confused :banghead:

Any help would be much appreciated.

Thanks in advance
Dan

ConnorGiles · Dec 3, 2014

would have a go at this for you Dan.

But I would need more information to go from.

Would you be able to show me in a sample document what you are trying to achieve. and maybe a sample of your code you are currently using

Dan25e · Dec 3, 2014

Hi ConnorGiles

Thanks for the quick reply.

Looks like I need to post 10 times before I can put links to dropbox in here!

Can I PM you?

Cheers
Dan

marlan · Dec 3, 2014

Hi,
Pardon my question: e-mail subject is a string value. Are you asking how to to set values to a string?
Please focus on the complications: how do you send the e-mail, witch object do you use? where is it declared? where is subject located? where in the form do you take the ID value from?
Then ask yourself (or the forum) why won't the data pass from there to ether.

All the best!

ConnorGiles · Dec 3, 2014

Feel free dan.

burrina · Dec 3, 2014

Unsure of your setup, but here is 2 examples:

Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim strBcc As String

strSubject = Me.EmailSubject
DoCmd.SendObject , , , strToWhom, , strBcc, strSubject, strMsgBody, True

DoCmd.SendObject acSendNoObject, "FormTyp", acFormatHTML, , , , , DLookup("[EMailMessage]", "tblCompany"), True, ""[/QUOTE]

HTH

smig · Dec 3, 2014

can you put the code for sending the email ?
Mostly focus on setting the subject line.

Do you send the mail using a button on the form ?

Dan25e · Dec 3, 2014

smig said:
can you put the code for sending the email ?
Mostly focus on setting the subject line.

Do you send the mail using a button on the form ?

Hi smig,

I've used a button on the form that fires a macro to send the email (see below - from Macro Builder

RunCommand - SaveRecord
SendObject - Report, Projects NCR Notification, PDF Format (*.pdf),...

Cheers
Dan

Dan25e · Feb 4, 2015

Hi folks,
Thanks for your help thus far...

Further to the above, I've converted my Forms macros to VBA and looks like this...

Private Sub FwdEmail_Click()
On Error GoTo FwdEmail_Click_Err

DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acReport, "Supervisor Notification IR No here", "PDFFormat(*.pdf)", "dan25e@flimflam.com", "dan25e@flimflam.com", "dan25e@flimflam.com", "Attempt to pass IR No here", "Add some text along with IR No here", True, ""

FwdEmail_Click_Exit:
Exit Sub

FwdEmail_Click_Err:
MsgBox Error$
Resume FwdEmail_Click_Exit

End Sub

'IR No' is a field in the form that I want to appear in the email subject line and in the message body.

Any further pointers would be a great help.

Thanks
Dan

Minty · Feb 4, 2015

You refer to a controls value in a currently open form using Me.ControlName

You can experiment with this using debug.print me.controlname in the VBA code and looking at the immediate window.

CJ_London · Feb 4, 2015

if IR No is the name of your control on the form

..."Supervisor Notification " & me.IR_No ,...

Note the substitution of _ for the space. Nobody likes spaces - even VBA which will add one automatically if you use intellisense

Dan25e · Feb 5, 2015

CJ_London, you're a star. Works a treat.

Any chance of another tip?

When I click the relevant button on the form, the report email attachment 'Supervisor Notification' is based on a query which prompts for the IR No and it has to be manually entered. How can I get the query to take the IR No from the current record as its criteria?

Thanks in advance.

Dan25e · Feb 5, 2015

It's OK. I found a solution.
[Forms]![Form Name]![Field Name]
in the criteria of the relevant field in the query. No more copying and pasting or manual input!

Copy and paste. The final piece of the jigsaw! (1 Viewer)

Dan25e

Registered User.

ConnorGiles

Strange Traveller

Dan25e

Registered User.

marlan

Registered User.

ConnorGiles

Strange Traveller

burrina

Registered User.

smig

Registered User.

Dan25e

Registered User.

Dan25e

Registered User.

Minty

AWF VIP

CJ_London

Super Moderator

Dan25e

Registered User.

Dan25e

Registered User.

Similar threads

Users who are viewing this thread