E-mail macro

[nsharish20]

Hi,

I am a beginner with Access but I have managed to play around with VB and macros.

I have a query called "qryClosure". In this database, I have fields like Report #, Customer #, CA Resp, Defect Code etc.... and only ONE record. This query is linked to the report called rptCloseout and Report # from qryClosure is in this report. Now what I want to do is when I send a snapshot of this report to someone using the macro, I want to include this report # in the subject or filename without typing it in. Basically what I want the macro to do is to take the info (report #) from the qryClosure file -- > Report # field and put that number in the subject or filename.

Here is the code that I got along with the macro

Option Compare Database

'------------------------------------------------------------
' mcrEmailClosure
'
'------------------------------------------------------------
Function mcrEmailClosure()
On Error GoTo mcrEmailClosure_Err

' Email as SNP closure report
DoCmd.SendObject acReport, "rptCloseOut",
"SnapshotFormat(*.snp)",
"", "", "", "Closure Report - Concern Number", "Please see attached
closure report", True, ""

mcrEmailClosure_Exit:
Exit Function

mcrEmailClosure_Err:
MsgBox Error$
Resume mcrEmailClosure_Exit

End Function
---------------------------------------------------------------------------*-----------------------

Any ideas? Thanks

[JConsulting]

I am attaching some more files to give a better idea of what i am trying to do.

If there's no criteria for your query..then you can do this

Function mcrEmailClosure()
On Error GoTo mcrEmailClosure_Err
dim RptNum as string
 
RptNum = dlookup("[Report#]","qryClosure")
 
' Email as SNP closure report
DoCmd.SendObject acReport, "rptCloseOut",
"SnapshotFormat(*.snp)",
"", "", "", "Closure Report - Concern Number" & RptNum, "Please see attached
closure report", True, ""
 
mcrEmailClosure_Exit:
Exit Function
 
mcrEmailClosure_Err:
MsgBox Error$
Resume mcrEmailClosure_Exit
 
End Function
 

If you have criteria, you just need to add it to the Dlookup line
J

[nsharish20]

Does this lookup function copy the report # from the query and put it in the subject line? I'll be testing your code tomorrow to see how it works and i'll report back to you. Thanks

Here is a picture of the query if it helps.

Thanks for your help

[JConsulting]

Does this lookup function copy the report # from the query and put it in the subject line? I'll be testing your code tomorrow to see how it works and i'll report back to you. Thanks

Here is a picture of the query if it helps.

Thanks for your help

Like I said, if your criteria has no crietria...then yes.
J

[nsharish20]

Hi
Your code works. Thanks a lot.

One more thing. Can you please tell me how I can convert a module into a macro. The previous version of the code I gave you came from the macro which I converted to VB. In the macro, I had the option to select the action called SendObject and fill up the parameters.
Now I want to create a button in the toolbar to execute this code. If you can tell me that, that would be awesome.

Thanks for your help.

[JConsulting]

Hi
Your code works. Thanks a lot.

One more thing. Can you please tell me how I can convert a module into a macro. The previous version of the code I gave you came from the macro which I converted to VB. In the macro, I had the option to select the action called SendObject and fill up the parameters.
Now I want to create a button in the toolbar to execute this code. If you can tell me that, that would be awesome.

Thanks for your help.

You can create a macro to run your code using the Run Code option.

If your function for example is called RunBob, then where it asks you what the name of the code is....put RunBob()

That should take care of it.

Make sure your converted macro is defined as a function, and in a public module. If it's in a form module for example then if it's not public..it won't find it.

J

[nsharish20]

Thanks a lot for your help. Everything worked fine and I owe it all to you.

[JConsulting]

Thanks a lot for your help. Everything worked fine and I owe it all to you.

Happy to help.
J