By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,020 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

E-mail macro

P: 14
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
May 10 '07 #1
Share this Question
Share on Google+
7 Replies


JConsulting
Expert 100+
P: 603
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

Expand|Select|Wrap|Line Numbers
  1. Function mcrEmailClosure()
  2. On Error GoTo mcrEmailClosure_Err
  3. dim RptNum as string
  4.  
  5. RptNum = dlookup("[Report#]","qryClosure")
  6.  
  7. ' Email as SNP closure report
  8. DoCmd.SendObject acReport, "rptCloseOut",
  9. "SnapshotFormat(*.snp)",
  10. "", "", "", "Closure Report - Concern Number" & RptNum, "Please see attached
  11. closure report", True, ""
  12.  
  13. mcrEmailClosure_Exit:
  14. Exit Function
  15.  
  16. mcrEmailClosure_Err:
  17. MsgBox Error$
  18. Resume mcrEmailClosure_Exit
  19.  
  20. End Function
  21.  
If you have criteria, you just need to add it to the Dlookup line
J
May 10 '07 #2

P: 14
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
Attached Images
File Type: jpg query.JPG (66.4 KB, 213 views)
May 10 '07 #3

JConsulting
Expert 100+
P: 603
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
May 10 '07 #4

P: 14
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.
May 10 '07 #5

JConsulting
Expert 100+
P: 603
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
May 10 '07 #6

P: 14
Thanks a lot for your help. Everything worked fine and I owe it all to you.
May 10 '07 #7

JConsulting
Expert 100+
P: 603
Thanks a lot for your help. Everything worked fine and I owe it all to you.
Happy to help.
J
May 10 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.