EMailDatabaseObject to recipient in a query (1 Viewer)

[swb147]

I am trying to put a macro that emails a report to a recipient selected from a combo box, is this possible?

I can get it to email a report by either leaving the To: box empty and letting the person sending the email to put the email address in but i thought i might be able to run a query from a combo box to insert the email address in question.

 

[GinaWhipp]

I don't know about a Macro but perhaps you could use...

Dim vRecipient As String
Dim vMsg As String
Dim vSubject As String
 
vMsg = " Your Message here... "
vSubject = " Your Subject here... "
vRecipient = Me.YourComboBox

 
DoCmd.SendObject acSendReport, " rptYourReport ", acFormatPDF, vRecipient, , , vSubject, vMsg, False

 

[Holly_Associated]

Hi Gina / All,

Thank you for this amazing snippet! I usually use macros but have used this code as there isn't a macro which can do the same thing. I have amended it as follows to suit my need and have attached it to the e-mail address in a text box (no, I couldn't format to hyperlink... anyhoo...)

Private Sub txtEMail_Click()
Dim vRecipient As String
Dim vMsg As String
Dim vSubject As String
 
vMsg = " Your Message here... "
vSubject = " Your Subject here... "
vRecipient = Me.txtEMail
 
DoCmd.SendObject acSendNoObject, , , vRecipient, , , vSubject, vMsg, True
End Sub

My problem is, if I click the text, my e-mail opens as expected, I can edit and send if I want... but if I press close because I don't want to send, Access gives me the attached error.
My question is, how do I stop this?

Any help / pointers much appreciated

 

[pr2-eugin]

You need a proper Error Handler routine to do that for you..

Private Sub txtEMail_Click()
[COLOR=Blue][B]On Error GoTo errHandler[/B][/COLOR]
    Dim vRecipient As String
    Dim vMsg As String
    Dim vSubject As String
    
    vMsg = " Your Message here... "
    vSubject = " Your Subject here... "
    vRecipient = Me.txtEMail
    
    DoCmd.SendObject acSendNoObject, , , vRecipient, , , vSubject, vMsg, True
[COLOR=Blue][B]exitOnErr[/B][COLOR=Red]:[/COLOR][B]
    Exit Sub
errHandler:
    If Err.Number <> 2501 Then MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical
    Resume exitOnErr[/B][/COLOR]
End Sub

 

[Holly_Associated]

Hi Paul,
Thank you for taking the time to reply, I really appreciate it and the link is very helpful. I am getting the attached error when I paste this in though; it seems to be the exitOnErr bit causing the problem? (I assume as it highlights it?)

 

[pr2-eugin]

So sorry, just changed the code.. The exitOnErr needed a : at the end..

 

[Holly_Associated]

Don't apologise! It's nice to know the experts miss a colon every now and then It works perfectly, thank you.

If you're up for explaining a bit more, I have been a bit ambitious and tried to apply this to a button:
The attached "Form" has a print preview button which runs the attached "Macro" on click. The report which opens depends on the MSID as shown with the "if" (which I am very proud of, it works!).
I have added an extra button on the form with a little envelope.
I would like it to open an e-mail and automatically attach the report which the macro would open. I tried the below and got the attached "error". I fear it is not as simple as I hoped it would be!

Private Sub Command68_Click()
On Error GoTo errHandler
    Dim vRecipient As String
    Dim vMsg As String
    Dim vSubject As String
 
    vMsg = " Your Message here... "
    vSubject = " Your Subject here... "
 
    DoCmd.SendObject acSendReport, [COLOR=blue]"DoCmd.RunMacro(IfRAMS)[/COLOR]", acFormatPDF, , , , vSubject, vMsg, True
exitOnErr:
    Exit Sub
errHandler:
    If Err.Number <> 2501 Then MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical
    Resume exitOnErr
End Sub

 

[pr2-eugin]

I am not a fan of Macro's, hard to use, hard to debug.. I prefer VBA, so I have never used Macros from VBA. That being said, your Macro opens the report, you need to send the appropriate report, not the Macro..

I am just going to assume you have changed your mind about using Macros and you are going to start VBA..

Private Sub Command68_Click()
On Error GoTo errHandler
    Dim vRecipient As String, vMsg As String, vSubject As String
    Dim rptName As String, strWhere As String
    
    If Me.MSID = "MS01" Then
        rptName = "RptMS01"
    Else
        rptName = "RptMS02"
    End If
    strWhere = "RAMSID = " & Forms!FrmMenu!NavigationSubform!SubFrmJobMS!RAMSID
   [COLOR=Green] 'I have assumed that RAMSID is number if it is String. use the following..
    'strWhere = "RAMSID = '" & Forms!FrmMenu!NavigationSubform!SubFrmJobMS!RAMSID & "'"[/COLOR]
    
    vMsg = " Your Message here... "
    vSubject = " Your Subject here... "
    DoCmd.OpenReport rptName, acViewPreview, , strWhere
    DoCmd.SendObject acSendReport, rptName, acFormatPDF, , , , vSubject, vMsg, True
exitOnErr:
    Exit Sub
errHandler:
    If Err.Number <> 2501 Then MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical
    Resume exitOnErr
End Sub

 

[Holly_Associated]

Hehe! It works, and I can understand it! Changing the If to code wasn't as painful as I thought .

I've updated it so that I can add more If's, I used Else if and it seems to be OK. Eventually the MS's will go up to MS15, is it OK to use this many If's in code?

Also, could you explain "Me." for me pretty please? Does it just point to the current record?

Private Sub Command68_Click()
On Error GoTo errHandler
    Dim vRecipient As String, vMsg As String, vSubject As String
    Dim rptName As String, strWhere As String
 
    If Me.MSID = "MS01" Then
        rptName = "RptMS01"
    Else: If Me.MSID = "MS02" Then rptName = "RptMS02"
 
    End If
    strWhere = "RAMSID = " & Forms!FrmMenu!NavigationSubform!SubFrmJobMS!RAMSID
    'I have assumed that RAMSID is number if it is String. use the following..
    'strWhere = "RAMSID = '" & Forms!FrmMenu!NavigationSubform!SubFrmJobMS!RAMSID & "'"
 
    vMsg = " Your Message here... "
    vSubject = " Your Subject here... "
    DoCmd.OpenReport rptName, acViewPreview, , strWhere
    DoCmd.SendObject acSendReport, rptName, acFormatPDF, , , , vSubject, vMsg, True
exitOnErr:
    Exit Sub
errHandler:
    If Err.Number <> 2501 Then MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical
    Resume exitOnErr
End Sub

Thank you so much for your help Paul

 

[pr2-eugin]

Hehe! It works, and I can understand it! Changing the If to code wasn't as painful as I thought .

Glad you have the hang of things now.

I've updated it so that I can add more If's, I used Else if and it seems to be OK. Eventually the MS's will go up to MS15, is it OK to use this many If's in code?

Do not use multiple IF's, if they are going to be all in the format of RptMSXX then use the following code..

Private Sub Command68_Click()
On Error GoTo errHandler
    Dim vRecipient As String, vMsg As String, vSubject As String
    Dim rptName As String, strWhere As String
    
    [COLOR=Red][B]rptName = "RptMS" & [URL="http://www.techonthenet.com/access/functions/string/right.php"]Right[/URL](Me.MSID, 2)[/B][/COLOR]
    
    strWhere = "RAMSID = " & Forms!FrmMenu!NavigationSubform!SubFrmJobMS!RAMSID
   [COLOR=Green] 'I have assumed that RAMSID is number if it is String. use the following..
    'strWhere = "RAMSID = '" & Forms!FrmMenu!NavigationSubform!SubFrmJobMS!RAMSID & "'"[/COLOR]
 
    vMsg = " Your Message here... "
    vSubject = " Your Subject here... "
    DoCmd.OpenReport rptName, acViewPreview, , strWhere
    DoCmd.SendObject acSendReport, rptName, acFormatPDF, , , , vSubject, vMsg, True
exitOnErr:
    Exit Sub
errHandler:
    If Err.Number <> 2501 Then MsgBox "Error (" & Err.Number & ") - " & Err.Description, vbCritical
    Resume exitOnErr
End Sub

Saying that, why do you have so many reports? If in future there are 15 more would you go ahead and create 15 more reports? You need to think of Robust structure.

Also, could you explain "Me." for me pretty please? Does it just point to the current record?

Me.someControl is the shortest way of representing Forms!FormName!someControl, also Me. represents the current object. It is best used as you will be able to make use of the intellisense, making sure that the control/property/event you are trying to access actually exisits and avoid any spelling mistakes.

 

[Holly_Associated]

The RptMSXX are Method Statements. Each one I've typed out the "static" text, so MS01 is for dismantling works, MS02 is for carpentry works etc. and they are all bound to a query with all of the details. So each report has a space for [StartDate], [Site] etc.
Previously, we had these saved as 15 Word docs, each time you needed one for site you would have to go in, amend all of the details, pdf and send. Using this table, the 15 "Word Docs" or now reports are saved in the background, you enter the type of method statement you need with the details in the form's "table" and press the button and hey presto, no more sitting for hours typing out details . I now have a print preview button and an e-mail button .

I think I understand the "right" function, I've used it in Excel before. I didn't know some of the functions are transferable, although I've found out some of them aren't! Date() and Today(), really Microsoft!

 

[GinaWhipp]

Well, took me a few days to get back but I see you are in the capable hands of Paul, so I'll be moving along!