Resubmit: DoCmd.SendObject (1 Viewer)

PatAccess

Registered User.
Local time
Today, 18:51
Joined
May 24, 2017
Messages
284
Hello Guys,

I am trying to send emails with the DoCmd.SendObject method. I understand the concept but would like to know how I can tweak it a little.
Here is my code:
Code:
Code:
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim varObjName As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_EngineerLicRenewal-Emails", dbOpenDynaset, dbSeeChanges)
varObjName = DoCmd.OpenReport("Rpt_LCARenew", acViewPreview, , Employee = "& rs!Employee &")

DoCmd.SendObject acSendReport, varObjName, acFormatPDF, "email", , , "SendObject Email Test", "This is just a test to see what how it looks", True

End Sub
Of Course, it is giving me an error message of "Compile error: Expected Function or variable" for this "varObjName = DoCmd.OpenReport("Rpt_LCARenew", acViewPreview, , Employee = "& rs!Employee &")
"
How can I create a variable to hold the report name as a DoCmd object so that it will attach the report of that person and email that person, etc?
Thank you for your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
Hi Pat. I thought I posted something about this earlier. Have you tried it this way?
Code:
...
varObjName = "Rpt_LCARenew"
DoCmd.OpenReport varObjName, acViewHidden, , "Employee=" & rs!Employee
DoCmd.SendObject acSendReport, varObjName, acFormatPDF, ...
DoCmd.Close acReport, varObjName
...
 

PatAccess

Registered User.
Local time
Today, 18:51
Joined
May 24, 2017
Messages
284
Thank you theDBguy . that worked without the previous error but it not opening the pdf with my condition. This piece
Code:
DoCmd.OpenReport varObjName, acViewPreview, , "Employee = " & rs!Employee & ""
Any idea why it would do this?
Thanks again
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:51
Joined
May 7, 2009
Messages
19,169
is employee a text?

Dim rs As DAO.Recordset

...
DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
 

PatAccess

Registered User.
Local time
Today, 18:51
Joined
May 24, 2017
Messages
284
Yes arnelgp, it's a text and I made the revision and now it opens properly BUT I now placed a Recordset in my code to get it to email each person from the query with their own personal report. However, when I run the program, it keeps opening a new outlook window with the 1st email, over and over again without stopping and it is not moving through the list. Why?
Code:
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim varObjName As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_EngineerLicRenewal-Emails", dbOpenDynaset, dbSeeChanges)

If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        If IsNull(rs!Email) Then
            rs.MoveNext
        Else
            varObjName = "Rpt_LCARenew"
            On Error Resume Next
            DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
            DoCmd.SendObject acSendReport, varObjName, acFormatPDF, rs!Email, , , "SendObject Email Test", "This is just a test to see what how it looks", True
            DoCmd.Close acReport, varObjName
        End If
    Loop
End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:51
Joined
May 7, 2009
Messages
19,169
you need to Move through the records.
Code:
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim varObjName As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_EngineerLicRenewal-Emails", dbOpenDynaset, dbSeeChanges)

varObjName = "Rpt_LCARenew"
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF
        If Trim(rs!Email.Value & "") <> "" Then
            On Error Resume Next
            DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
            DoCmd.SendObject acSendReport, varObjName, acFormatPDF, rs!Email, , , "SendObject Email Test", "This is just a test to see what how it looks", True
            DoCmd.Close acReport, varObjName
        End If
        rs.MoveNext
    Loop
End If
End Sub
 

PatAccess

Registered User.
Local time
Today, 18:51
Joined
May 24, 2017
Messages
284
Question arnelgp?
Why did you use? Can you comment them out so I see what you did, please?
Code:
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF
        If Trim(rs!Email.Value & "") <> "" Then
            rs.MoveNext
And not
Code:
if rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        If IsNull(rs!Email) Then
            rs.MoveNext
Is it the reason, it is skipping a record when reading it?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:51
Joined
May 7, 2009
Messages
19,169
if both .Eof and .Bof is true, there is no record.

with rs.RecordCount, you need to make sure you .MoveLast and .MoveFirst to get
the correct Recordcount. but you will get error, when you move your record pointer to a recordset with no record?!
 

PatAccess

Registered User.
Local time
Today, 18:51
Joined
May 24, 2017
Messages
284
Ok but why is it skipping the next line. I commented out "On Error Resume Next" and the code states that "DoCmd.SendObject" was cancelled so it skips the first line and then returns the 2nd, skips the 3rd, returns the 4th. Why is it doing that?
Code:
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim varObjName As Variant
Dim strEmailMsg As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_EngineerLicRenewal-Emails", dbOpenDynaset, dbSeeChanges)

varObjName = "Rpt_LCARenew"
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF
        If Trim(rs!Email.Value & "") <> "" Then
            rs.MoveNext
            On Error Resume Next
            strEmailMsg = "Hello " & rs!FirstName & "," & vbCr & vbCr & _
                            "You have an upcoming and/or expired License/Certification. Please see the attached report(s)." & vbCr & vbCr & _
                            "Please forward your new expiration date(s) and/or your intent to not renew. I will update the database accordingly." & vbCr & vbCr & _
                            "Thank you"
            DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
            DoCmd.SendObject acSendReport, varObjName, acFormatPDF, rs!Email, , , "Upcoming and/or Expired License/Certification Reminder for " & rs!FullName, strEmailMsg, True
            DoCmd.Close acReport, varObjName
        End If
        rs.MoveNext
    Loop
End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
Thank you theDBguy . that worked without the previous error but it not opening the pdf with my condition. This piece
Code:
DoCmd.OpenReport varObjName, acViewPreview, , "Employee = " & rs!Employee & ""
Any idea why it would do this?
Thanks again
Hi. I didn't read the rest of the thread; but to answer this question, if you want to actually see/display the report, then use acViewPreview, like you originally did, in place of acViewHidden, as I had it in my post. Cheers!
 

PatAccess

Registered User.
Local time
Today, 18:51
Joined
May 24, 2017
Messages
284
Hi theDBguy,
Thank you, I changed that piece already but do you have any idea what is going with the previous question I've asked arnelgp.

Why is it skipping the next line. I commented out "On Error Resume Next" and the code states that "DoCmd.SendObject" was cancelled so it skips the first line and then returns the 2nd, skips the 3rd, returns the 4th. Why is it doing that?
Code:
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim varObjName As Variant
Dim strEmailMsg As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_EngineerLicRenewal-Emails", dbOpenDynaset, dbSeeChanges)

varObjName = "Rpt_LCARenew"
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF
        If Trim(rs!Email.Value & "") <> "" Then
            rs.MoveNext
            On Error Resume Next
            strEmailMsg = "Hello " & rs!FirstName & "," & vbCr & vbCr & _
                            "You have an upcoming and/or expired License/Certification. Please see the attached report(s)." & vbCr & vbCr & _
                            "Please forward your new expiration date(s) and/or your intent to not renew. I will update the database accordingly." & vbCr & vbCr & _
                            "Thank you"
            DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
            DoCmd.SendObject acSendReport, varObjName, acFormatPDF, rs!Email, , , "Upcoming and/or Expired License/Certification Reminder for " & rs!FullName, strEmailMsg, True
            DoCmd.Close acReport, varObjName
        End If
        rs.MoveNext
    Loop
End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,
Thank you, I changed that piece already but do you have any idea what is going with the previous question I've asked arnelgp.

Why is it skipping the next line. I commented out "On Error Resume Next" and the code states that "DoCmd.SendObject" was cancelled so it skips the first line and then returns the 2nd, skips the 3rd, returns the 4th. Why is it doing that?
Code:
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As Recordset
Dim varObjName As Variant
Dim strEmailMsg As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Qry_EngineerLicRenewal-Emails", dbOpenDynaset, dbSeeChanges)

varObjName = "Rpt_LCARenew"
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF
        If Trim(rs!Email.Value & "") <> "" Then
            [COLOR=red]rs.MoveNext[/COLOR]
            On Error Resume Next
            strEmailMsg = "Hello " & rs!FirstName & "," & vbCr & vbCr & _
                            "You have an upcoming and/or expired License/Certification. Please see the attached report(s)." & vbCr & vbCr & _
                            "Please forward your new expiration date(s) and/or your intent to not renew. I will update the database accordingly." & vbCr & vbCr & _
                            "Thank you"
            DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
            DoCmd.SendObject acSendReport, varObjName, acFormatPDF, rs!Email, , , "Upcoming and/or Expired License/Certification Reminder for " & rs!FullName, strEmailMsg, True
            DoCmd.Close acReport, varObjName
        End If
        [COLOR=red]rs.MoveNext[/COLOR]
    Loop
End If
End Sub
Hi. My guess why it's skipping the next "record" (line?) is because you are moving the record pointer twice, as I indicated above in your code.
 

Users who are viewing this thread

Top Bottom