Solved SQL/Recordset/Email (1 Viewer)

P

PatAccess

Registered User.
Local time
Yesterday, 20:52
Joined
May 24, 2017
Messages
284
Hello Guys,
I am trying to create a email with the body showing all of the person's information. I think I made a mess because not Access is stuck. Here is the code.
Code: 
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem

'Open the Recordset
Set db = CurrentDb
sqlStr = "SELECT [Employee],FName, FirstName,[State],RegistrationNo,DateExpires FROM QryPERenew WHERE [Employee]='" & Me.cboFName.Column(0) & "'"
Set rs = db.OpenRecordset(sqlStr)

'Create Email
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .To = Me.cboEmail
    .CC = "...."
    .Subject = "Expiring/Expired PE Licensing Renewals - " & rs!FName & Date
    Do While Not rs.EOF
    rs.MoveFirst
    rs.MoveNext
    .Body = "Hi " & rs!FirstName & "," & vbNewLine & _
            "You have and Expiring/Expired PE license on file. " & _
            "Please notify us of your intent to renew or not renew with new expiration date(s). " & vbNewLine & _
            "State          " & "Expiration Date" & vbNewLine & _
            rs!State & "    " & rs!DateExpires
    Loop
    .Display
End With

Set rs = Nothing
Set db = Nothing

End Sub
 
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 17:52
Joined
Mar 14, 2017
Messages
8,777
Where is it stuck? What line of code? What is the error? Need more information than "access is stuck"......
 
P

PatAccess

Registered User.
Local time
Yesterday, 20:52
Joined
May 24, 2017
Messages
284
Isaac said:
Where is it stuck? What line of code? What is the error? Need more information than "access is stuck"......
Click to expand...
It is not giving me an error. When I click on the cmd, it's just stuck and I have to do a ctrl+Alt+delete and reopen Access
 
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 17:52
Joined
Mar 14, 2017
Messages
8,777
You probably shouldn't keep doing MoveFirst inside your loop..

Take out the MoveFirst entirely.
Move the MoveNext to be the last line within the inner loop.

You're stuck in an infinite loop
 
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 17:52
Joined
Mar 14, 2017
Messages
8,777
And you probably want to be APPENDING to .Body, (or better yet, appending to a string variable for strbody, then assigning .Body to strbody after the inner loop), rather than just putting .Body, which will keep replacing the entire body with that loop iteration's values.
 
P

PatAccess

Registered User.
Local time
Yesterday, 20:52
Joined
May 24, 2017
Messages
284
Now it is giving me the previous result, which is only one records when there should be more. I want the rs!State and rs!DateExpires, only, to enumerate under "State" and "Expiration Date". So it will look like this
State Registration
Alabama 12/31/2020
DC 12/31/2020
etc. for that person. How can I accomplish that? Here is where my code is
Code: 
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim strBody As String

'Open the Recordset
Set db = CurrentDb
sqlStr = "SELECT [Employee],FName, FirstName,[State],RegistrationNo,DateExpires FROM QryPERenew WHERE [Employee]='" & Me.cboFName.Column(0) & "'"
Set rs = db.OpenRecordset(sqlStr)


'Create Email
Set oEmail = oApp.CreateItem(olMailItem)
strBody = "Hi " & rs!FirstName & "," & vbNewLine & _
            "You have and Expiring/Expired PE license on file. " & _
            "Please notify us of your intent to renew or not renew with new expiration date(s). " & vbNewLine & _
            "State          " & "Expiration Date" & vbNewLine & _
            rs!State & "    " & rs!DateExpires
With oEmail
    .To = Me.cboEmail
    .CC = "arlette.watson@rmf.com"
    .Subject = "Expiring/Expired PE Licensing Renewals - " & rs!FName & Date
    Do While Not rs.EOF
    .Body = strBody
    rs.MoveNext
    Loop
    .Display
End With

Set rs = Nothing
Set db = Nothing

End Sub
 
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 17:52
Joined
Mar 14, 2017
Messages
8,777
strBody needs to be INSIDE the inner loop, constantly being appended to. strBody will retain the assignment you have given it in your existing code. It won't automatically be reassigned during the loop the way you have it - that's not how variables work.

General structure more like:

Code: 
strBody = Starter values the way you have done it there
Loop begins
   strBody = strBody & vbnewline & [more values]
Loop ends

mail body = strBody
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:52
Joined
Sep 21, 2011
Messages
14,265
If you indent your code properly, it makes it much easier to spot errors, especially when not that experienced.?
Code: 
Private Sub cmdEmail_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlStr As String
    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    Dim strBody As String

    'Open the Recordset
    Set db = CurrentDb
    sqlStr = "SELECT [Employee],FName, FirstName,[State],RegistrationNo,DateExpires FROM QryPERenew WHERE [Employee]='" & Me.cboFName.Column(0) & "'"
    Set rs = db.OpenRecordset(sqlStr)


    'Create Email
    Set oEmail = oApp.CreateItem(olMailItem)
    strBody = "Hi " & rs!FirstName & "," & vbNewLine & _
              "You have and Expiring/Expired PE license on file. " & _
              "Please notify us of your intent to renew or not renew with new expiration date(s). " & vbNewLine & _
              "State          " & "Expiration Date" & vbNewLine & _
              rs!State & "    " & rs!DateExpires
    With oEmail
        .To = Me.cboEmail
        .CC = "arlette.watson@rmf.com"
        .Subject = "Expiring/Expired PE Licensing Renewals - " & rs!FName & Date
        Do While Not rs.EOF
            .body = strBody
            rs.MoveNext
        Loop
        .display
    End With

    Set rs = Nothing
    Set db = Nothing

End Sub
 
P

PatAccess

Registered User.
Local time
Yesterday, 20:52
Joined
May 24, 2017
Messages
284
Isaac said:
strBody needs to be INSIDE the inner loop, constantly being appended to. strBody will retain the assignment you have given it in your existing code. It won't automatically be reassigned during the loop the way you have it - that's not how variables work.

General structure more like:

Code: 
strBody = Starter values the way you have done it there
Loop begins
   strBody = strBody & vbnewline & [more values]
Loop ends

mail body = strBody
Click to expand...

You're a Genius. It works. Thank you so much!

Here is the new working Code:
Code: 
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim strBody As String

'Open the Recordset
Set db = CurrentDb
sqlStr = "SELECT [Employee],FName, FirstName,[State],RegistrationNo,DateExpires FROM QryPERenew WHERE [Employee]='" & Me.cboFName.Column(0) & "'"
Set rs = db.OpenRecordset(sqlStr)

'Create Email
Set oEmail = oApp.CreateItem(olMailItem)
strBody = "Hi " & rs!FirstName & "," & vbNewLine & _
            "You have and Expiring/Expired PE license on file. " & _
            "Please notify us of your intent to renew or not renew with new expiration date(s). " & vbNewLine & _
            "State              " & "Expiration Date"

With oEmail
    .To = Me.cboEmail
    .CC = "......."
    .Subject = "Expiring/Expired PE Licensing Renewals - " & rs!FName & Date
    Do While Not rs.EOF
        strBody = strBody & vbNewLine & _
        rs!State & "                        " & rs!DateExpires
        rs.MoveNext
    Loop
    .Body = strBody
    .Display
End With

Set rs = Nothing
Set db = Nothing

End Sub
 
Last edited:
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 17:52
Joined
Mar 14, 2017
Messages
8,777
PatAccess said:
You're a Genius. It works. Thank you so much!

Here is the new working Code:
Code: 
Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlStr As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim strBody As String

'Open the Recordset
Set db = CurrentDb
sqlStr = "SELECT [Employee],FName, FirstName,[State],RegistrationNo,DateExpires FROM QryPERenew WHERE [Employee]='" & Me.cboFName.Column(0) & "'"
Set rs = db.OpenRecordset(sqlStr)

'Create Email
Set oEmail = oApp.CreateItem(olMailItem)
strBody = "Hi " & rs!FirstName & "," & vbNewLine & _
            "You have and Expiring/Expired PE license on file. " & _
            "Please notify us of your intent to renew or not renew with new expiration date(s). " & vbNewLine & _
            "State              " & "Expiration Date"

With oEmail
    .To = Me.cboEmail
    .CC = "arlette.watson@rmf.com"
    .Subject = "Expiring/Expired PE Licensing Renewals - " & rs!FName & Date
    Do While Not rs.EOF
        strBody = strBody & vbNewLine & _
        rs!State & "                        " & rs!DateExpires
        rs.MoveNext
    Loop
    .Body = strBody
    .Display
End With

Set rs = Nothing
Set db = Nothing

End Sub
Click to expand...
Glad you got it working! (y)
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 01:52
Joined
Sep 21, 2011
Messages
14,265
@PatAccess
Wouldn't a vbTab or two be better that just a set number of spaces?
 
Isaac

Isaac

Lifelong Learner
Local time
Yesterday, 17:52
Joined
Mar 14, 2017
Messages
8,777
That's an interesting question Gasman and will be curious to see how OP responds, if they choose to test.
In the past, I have noticed that when I have tried to use a Tab character in an Outlook mail body, it wasn't how I expected.
I expected that perhaps Outlook might auto-magically do what it does if you are using the GUI, and you type two lines of text, each with a starting chunk, then a tab, then an ending chunk...instead of representing one exact, actual tab space in between them, it rather "matches" the 2nd to the first. Like other Office GUI's and even SSMS.

But when I have tried to do that in VBA, it doesn't do that automagical goodness, it actually inserts a literal, exact, actual tab in both cases...meaning that, unless of course the two chunks of beginning text on each line are identical, the 2nd chunks of text are in different places.
I have only resolved it by using combinations of Left(), Right(), and padding numerous spaces to get them to come out the same.
 
You must log in or register to reply here.

Similar threads

N
Solved How to avoid the domain functions in Ms Access
Replies
13
Views
561
nector
N
M
Multiple attachments and a report in the same email
Replies
15
Views
938
Marshall Brooks
M
N
Solved Storing received Json from the internet into Parent table & child table at one go
2
Replies
21
Views
655
nector
N
C
Invalid Use of Null
2
Replies
20
Views
527
chuckcoleman
C
oxicottin
Solved Limit number of rows in a message box
Replies
7
Views
452
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom