Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-12-2019, 07:24 AM   #1
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 159
Thanks: 117
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Resubmit: DoCmd.SendObject

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

PatAccess is offline   Reply With Quote
Old 10-12-2019, 07:30 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,848
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Resubmit: DoCmd.SendObject

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
...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
PatAccess (10-21-2019)
Old 10-21-2019, 04:33 AM   #3
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 159
Thanks: 117
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Resubmit: DoCmd.SendObject

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

PatAccess is offline   Reply With Quote
Old 10-21-2019, 04:38 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Resubmit: DoCmd.SendObject

is employee a text?

Dim rs As DAO.Recordset

...
DoCmd.OpenReport varObjName, acViewPreview, , "Employee = '" & rs!Employee & "'"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
PatAccess (10-21-2019)
Old 10-21-2019, 05:00 AM   #5
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 159
Thanks: 117
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Resubmit: DoCmd.SendObject

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
PatAccess is offline   Reply With Quote
Old 10-21-2019, 05:03 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Resubmit: DoCmd.SendObject

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
PatAccess (10-21-2019)
Old 10-21-2019, 06:12 AM   #7
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 159
Thanks: 117
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Resubmit: DoCmd.SendObject

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?

PatAccess is offline   Reply With Quote
Old 10-21-2019, 06:16 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Resubmit: DoCmd.SendObject

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?!
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
PatAccess (10-21-2019)
Old 10-21-2019, 07:18 AM   #9
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 159
Thanks: 117
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Resubmit: DoCmd.SendObject

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
PatAccess is offline   Reply With Quote
Old 10-21-2019, 07:23 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,848
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Resubmit: DoCmd.SendObject

Quote:
Originally Posted by PatAccess View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
PatAccess (10-21-2019)
Old 10-21-2019, 09:48 AM   #11
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 159
Thanks: 117
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: Resubmit: DoCmd.SendObject

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
PatAccess is offline   Reply With Quote
Old 10-22-2019, 07:12 AM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,848
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Resubmit: DoCmd.SendObject

Quote:
Originally Posted by PatAccess View Post
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
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DoCmd.SendObject help beginner123 General 1 07-23-2013 04:20 PM
DoCmd.SendObject Eljefegeneo Modules & VBA 5 06-24-2013 06:13 PM
DoCmd.SendObject aliasghar Reports 12 10-19-2011 05:26 AM
docmd.sendobject flomar Modules & VBA 1 09-14-2004 01:07 AM
DoCmd.SendObject aziz rasul Modules & VBA 2 11-30-2000 06:26 AM




All times are GMT -8. The time now is 04:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World