Report in Email body

[Kraj]

I know this had been addressed before but a.) all the previous posts were specific to a particular problem or the problem was far more comlpex than what I want to do and b.) I'm not very good with VBA, so if it possible to piece together how to to a simple report in the body of an email from the code in previous posts, I was unable to do it.

All I want to do is put a text report into the body of an email. There is no formatting neccessary. The only cavaet, which prevents me from hard-coding text into the body, is the text needs to contain fields. What I haev is this:

A data input form.
A query that pulls some of the fields from the record for the current form.
A report that has some text, with fields from the query inserted, like a form letter. ie. Dear [Employee_Name],
A button on the form to run the email code.

Currently I used a SendOjbect macro to output the report as an rtf attachment and uses one of the fields for the To:, which works fine, but ideally I'd like the text in the body. I was able to code enough (based on the afforementioned posts) to open Outlook, create an email, set the To: to the employee name based on the form, and set the subject line. I just can't figure out the report-in-the-body bit.

Any help is greatly appreciated!

 

[The Stoat]

I know this had been addressed before but a.) all the previous posts were specific to a particular problem or the problem was far more comlpex than what I want to do and b.) I'm not very good with VBA, so if it possible to piece together how to to a simple report in the body of an email from the code in previous posts, I was unable to do it.

All I want to do is put a text report into the body of an email. There is no formatting neccessary. The only cavaet, which prevents me from hard-coding text into the body, is the text needs to contain fields. What I haev is this:

A data input form.
A query that pulls some of the fields from the record for the current form.
A report that has some text, with fields from the query inserted, like a form letter. ie. Dear [Employee_Name],
A button on the form to run the email code.

Currently I used a SendOjbect macro to output the report as an rtf attachment and uses one of the fields for the To:, which works fine, but ideally I'd like the text in the body. I was able to code enough (based on the afforementioned posts) to open Outlook, create an email, set the To: to the employee name based on the form, and set the subject line. I just can't figure out the report-in-the-body bit.

Any help is greatly appreciated!

Kraj, this should work.

Public Sub SendMail()
Dim outApp As Outlook.Application 
Dim outItem As Outlook.MailItem
Dim strEMailRec As String 'recipient
Dim strEMailTitle As String ' title
Dim strEMailMsg As String ' message text
Dim Db as Dao.Database
Dim Rst as Dao.Recordset

' SET YOUR DB INSTANCE
Set Db = Currentdb
'CREATE YOUR RECORDSET
Set Rst = Db.Openrecordset("SELECT TBL_USER.* FROM TBL_USER;")



' Use your details from the recordset
    strEMailRec = Rst![UserName]
    strEMailTitle = Rst![..]

   strEMailMsg = Rst![Msg]

Set outApp = New Outlook.Application
Set outItem = outApp.CreateItem(olMailItem)

    With outItem
        .To = strEMailRec
        .Subject = strEMailTitle
        .HTMLBody = strEMailMsg
        .Display
    End With

Set Db = Nothing
Set Rst = Nothing
Set outItem = Nothing
Set outApp = Nothing
End Sub

The Stoat

 

[Kraj]

Kill me now

Thanks for the help so far. I've gotten it to do almost everything that I wanted, but there is one last hurdle and (for me at least) it's a doozie. Here's what I ended up doing:

The needed data was pulled directly off the current form instead of relying on a report. There is one piece of data needed yet - an account balance. The account balance is currently calculated for otehr forms and reports by querying the transactions for the year, summing them, and subtracting the sum from $10,000 to create a remaining balance. This has always been done with a text box on the form or within the report. I'm confident I could get the summed field done within the query, if I could get the code to accept the query.

The query would have to select several fields from a single table and use criteria, especially matching the employee ID with the current form. It would then have to sum the dollar amount field and subtract it from $10,000. The resulting number would be insterted into the body text of the email. My code currently looks like this:

Private Sub cmdEmailConfirmation_Click()

   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment
   Dim Db As DAO.Database
   Dim Rst As DAO.Recordset

   Set Db = CurrentDb
   Set Rst = Db.OpenRecordset("qryMatching_Balance")
   
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(Me.[Employee].[Column](2))
      objOutlookRecip.Type = olTo

      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("Greg Krajenta")
      objOutlookRecip.Type = olBCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Matching Gifts Confirmation"
      .Body = Me.[Employee].Column(3) & " " & Me.[Employee].Column(2) & "," & vbNewLine & vbNewLine _
      & "Nuveen has matched your contribution to " & Me.[Organization].[Column](1) & _
      " for the amount of $" & Me.Contribution_Amount & "." & vbNewLine & vbNewLine _
      & "It was sent on " & Me.Contribution_Date & "." & vbNewLine & vbNewLine & _
      "Your balance available to be matched for the year is: " _
      & vbNewLine & vbNewLine & "Please contact Greg Krajenta at ext. 7702 or Thea Janus at ext. 7937 with any questions." & vbCrLf & vbCrLf
    
      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

Everything works perfectly, except the "too few parameters" error from the recordset. Any thoughts on how this code-neanderthal might go about making this work?

 

[The Stoat]

Hi Kraj,

4 questions.to be clear in my head

1. Is the ("qryMatching_Balance") made up of sub queries?

2. If so do any of them use parameters?

3. Are these parameters based on values in an open form or report?

4. This query will run from the Db window?
TS

PS
Read your PM's

 

[The Stoat]

Sod that it's late, here's the answer (well most of it!)

Public Function getval() As Date
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim QRYdef As DAO.QueryDef


Set Db = CurrentDb
Set QRYdef = Db.QueryDefs!Q3

' I had a combo box as the parameter in the query i replace it with [PARAM]

QRYdef.Parameters![PARAM] = Forms![Form1]![Combo0]

Set Rst = QRYdef.OpenRecordset
MsgBox Rst.Fields(0)

Set Rst = Nothing
Set Db = Nothing


End Function

This works fine so you can call a qry with parameters and you won't get the error messages . F-knows about data type mismatches you'll just have to play with it.

10:15 in the Uk off to bed - need all the beauty sleep i can get

Back tommorrow.

TS

 

[Kraj]

1. Is the ("qryMatching_Balance") made up of sub queries?

No.

3. Are these parameters based on values in an open form or report?

Three of the four fields use criteria. The name/employee ID field gets its value from the current form. The date field uses a preset range. Then there is a category field which chooses a particular category. The only field to reference an object other than the one table is the ID, as it points to the form.

4. This query will run from the Db window?

Without a hitch.

QRYdef.Parameters![PARAM] = Forms![Form1]![Combo0]

Which entries here must be changed by me? I substituted "Forms![Form1]![Combo0]" with "Me.[Name]" where Name is my ID combo box. Is there anything else?

Also, now it looks like since I created a recordset, it won't see the fields on the form. I tried setting the focus back to the form but that didn't work. It's time for me to head home, but the next thing I'll try is explicitly referencing the form instead of using Me.xxxxxx. Hopefully that will be the answer.

*Sigh* There is a reason I don't program for a living Again, thanks for the assistance. Hopefully the more I pound through this stuff the more I'll actually be able to understand it and avoid future questions.

 

[Kraj]

And....problems still. Am I crazy or does the syntax for referencing database objects unexplainably change from function to function?

This works:
Set objOutlookRecip = .Recipients.Add(Me.[Employee].[Column](2))

This doesn't:
Set objOutlookRecip = .Recipients.Add(Forms!frmNew_Contribution.[Employee].[Column](2))

Nor does:
Set objOutlookRecip = .Recipients.Add([Forms]![frmNew_Contribution].[Employee].[Column](2))

Nor does:
Set objOutlookRecip = .Recipients.Add([frmNew_Contribution].[Employee].[Column](2))

 

[The Stoat]

And....problems still. Am I crazy or does the syntax for referencing database objects unexplainably change from function to function?

This works:
Set objOutlookRecip = .Recipients.Add(Me.[Employee].[Column](2))

This doesn't:
Set objOutlookRecip = .Recipients.Add(Forms!frmNew_Contribution.[Employee].[Column](2))

Nor does:
Set objOutlookRecip = .Recipients.Add([Forms]![frmNew_Contribution].[Employee].[Column](2))

Nor does:
Set objOutlookRecip = .Recipients.Add([frmNew_Contribution].[Employee].[Column](2))

These work if that's any help.

Forms("frmNew_Contribution").Employee.Column(2)

or

Forms!frmNew_Contribution.Employee.Column(2)

TS

 

[Kraj]

That seemed to straighten out that issue, but I have no idea how to work with recordsets and my searches on syntax, etc. have been fruitless. So here is my code as it is now, and maybe I can be enlightened.

Private Sub cmdEmailConfirmation_Click()

   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment
   Dim Db As DAO.Database
   Dim Rst As DAO.Recordset
   Dim QRYdef As DAO.QueryDef


   Set Db = CurrentDb
   Set QRYdef = Db.QueryDefs!qryMatching_Balance

' qryMatchingBalance will contain the field to be displayed in the email.
' I had a combo box as the parameter in the query i replace it with [PARAM]

    QRYdef.Parameters![PARAM] = Me.[Name]

    Set Rst = Db.OpenRecordset(QRYdef)
' This line is gibberish by now, I'm sure. I've tried all sorts of things with it 
' and I have no idea what it should look like.

   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   
   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(Me.[Employee].[Column](2))
      objOutlookRecip.Type = olTo

      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("Greg Krajenta")
      objOutlookRecip.Type = olBCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Matching Gifts Confirmation"
      .Body = Forms!frmNew_Contribution.Employee.Column(3) & " " & Forms!frmNew_Contribution.Employee.Column(2) & "," & vbNewLine & vbNewLine _
      & "Nuveen has matched your contribution to " & Forms!frmNew_Contribution.Organization.Column(1) & _
      " for the amount of $" & Forms!frmNew_Contribution.Contribution_Amount & "." & vbNewLine & vbNewLine _
      & "It was sent on " & Forms!frmNew_Contribution.Contribution_Date & "." & vbNewLine & vbNewLine & _
      [COLOR=Red]"Your balance available to be matched for the year is: "[/COLOR] & vbNewLine & vbNewLine _
      & "Please contact Greg Krajenta " & "at ext. 7702 or Thea Janus at ext. 7937 with any questions." _
      & vbCrLf & vbCrLf
' This all appears to be fine until you hit the red spot. Currently I have no
' data to follow the string; it should be the reference to the data field from
' qryMatching_Balance but I haven't put anything there that hasn't errored.

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
   Set Rst = Nothing
   Set Db = Nothing
End Sub
' The rest of the code is fine.

So, as I plod forward I do so only with through the assistance of charitable posters. Any consistent system of syntax eludes me. The code assister within VBA is worthless. Help files only explain what something does and never show you how to do it.

 

[The Stoat]

Hi,

Fear not recordsets are pretty simple - or maybe i just use them pretty simply-. I only use DAO and i think you kmow enough about that so I will progress.

Dim DB as DAO.Database
Dim Rst as DAO.Recordset


Set Db = CurrentDB

' create an instance of the current database

set Rst = Db.OpenRecordset(" write an SQL statement in here")

The default recordset is i believe the dynaset which means you can pretty much do anything to it add delete etc. There is snapshot for viewing data only and others. They are explained in the help file -honest They are entered as part of the openrecordset parameters.

If you need to add variables then do as you would for any string that needs variables adding remembering that if you use them as parameters to enclose them correctly i.e ...#" & Mydate & "#...

I tend to create at least the basics of them in the qry builder and then copy the SQL and ammend it as necessary saves me typing errors.



now we have a recordset.

We can move through the recordset

Do until Rst.EOF = True ' move until the End Of Recordset arrives i.e the last record

To access the fields within the recordset you can do one of 2 things.

Rst![fieldname] ' by entering the name of the field

Rst.Fields(0) ' or the fields position in the recordset. If it is a straight copy of a table then it will take the order that they are displayed in the table.

Rst.Edit allows you to edit a recordset assuming you've opened an editable
recordset type. i.e a record that already contains data

Rst.Addnew will let you add a new record. All rules within the database i.e
ref integrity and within the tables i.e no nulls in key fields need to be obeyed
otherwise you get errors

To move around a recordset then you can use
Rst.MoveLast Last record in the recordset

or Rst.MoveFirst First Record in the recordset

Rst.MoveNext Next record from the current position

Rst.MovePrevious Previous record from the current position

To count records use Rst.Movelast to "populate" the recordset and then Rst.RecordCount.

So to edit a field you could say
Do Until Rst.EOF = TRUE

If Rst![FirstName] = "John" then

Rst.Edit

Rst![Surname] = "Smith"


Rst.Update ' causes the action to take place

Rst.MoveNext

Loop

To add a new record

Rst.Addnew

Rst![Surname] = "Smith"

Rst.Update

You can copy an objects recordset such as a form by using the recordsetclone method i.e

Dim Rst As DAO.RecordSet
Set Rst = Me.RecordSetClone

To move to the current record when you have cloned the recordset

Rst.BookMark = me.BookMark

This means you can do calculations on the current record without actually changing the values. And may other things i'm sure.


You can save bookmarks in a Variant type Variable,

Dim VarBmark as Variant

VarBmark = Rst.Bookmark

VarBmark = Me.Bookmark

Beware though they are only good for the time that form is being viewed.
Or more likely they are only good until the recordset is required.
You just have to test it. Also some recordsets don't support bookmarks so there is a method - rst.Bookmarkable - to check first it is a boolean.

And that is pretty much what i know. You can get quite fancy with them but there is loads in here to help.

HTH

TS

 

[Kraj]

So it's not possible to use an existing query for a recordset? (Without copying the SQL into the code and tweaking it?)

 

[The Stoat]

So it's not possible to use an existing query for a recordset? (Without copying the SQL into the code and tweaking it?)

Hi,

Yes it certainly is possible. You just type the name of the query instead of the SQL string.

set db = currentdb

set rst = db.openrecordset("NameofQuery")


If you are using parameters in the query then use the method i posted earlier.

Public Function getval() As Date
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim QRYdef As DAO.QueryDef


Set Db = CurrentDb
Set QRYdef = Db.QueryDefs!Q3 'Q3 is the name of my query

' I had a combo box as the parameter in the query i replaced it with word 
'[PARAM] as you would for a normal parameter query, obviously you can use
'any word you think fit.


QRYdef.Parameters![PARAM] = Forms![Form1]![Combo0]

Set Rst = QRYdef.OpenRecordset
MsgBox Rst.Fields(0)

Set Rst = Nothing
Set Db = Nothing


End Function

On holiday now for a week hope everything goes OK

TS

 

[Kraj]

Odd...that's what I tried in the first place with no results. I'm trying a new approach now: I'm trying to get a DSum field on the form to get the number I need and then pull that field into the email like I did with the other fields. We'll see how it goes.

EDIT: I've solved the calculation problem using DSum. Here it is:

=DSum("[Contribution_Amount]","tbl_CONTRIBUTION","[Employee_ID] =" & Forms!frmNew_Contribution!Employee_ID & "and [Contribution_Type] = 'Matching'" & "and [Date_Sent] Between # " & '1-1-' & DatePart('yyyy',Date()) & " # AND # " & '12-31-' & DatePart('yyyy',Date()) & " #")

Ain't it fun? It pulls all amounts from the table that match the form's employee ID and is of the type "Matching" and falls in the data range or 1/1-12/31 of the current year. The syntax was a b*tch

So the result of the DSum is held in an invisible text box on the form and is referenced by the code for a calculation. I could probably do the calculation in the box as well, but I thought it was complicated enough. Thanks for the help!

 

[Kraj]

Yay! I win!

Here is my final code with comments galore for those with the same issue. The email is sent based on the current record of an open form.

Private Sub cmdEmailConfirmation_Click()

Dim objOutlook As Outlook.Application These 3 lines are standard
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim intBalance As Currency For use in my balance calculation
Dim intMaxBalance As Currency For use in my balance calculation

DoCmd.RunCommand acCmdSaveRecord This saves my record so my text box calculates

' Create the Outlook session. Standard again
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message. This gets the recipient's name from column 2 of my combo box
Set objOutlookRecip = .Recipients.Add(Me.[Employee].[Column](2))
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message. Adds my name as a BCC. Not really neccessary cuz it'll show up in my sent folder, but useful if you want to copy someone else in. I'll probably change the string to a reference in an admin table.
Set objOutlookRecip = .Recipients.Add("I PUT MY NAME HERE")
objOutlookRecip.Type = olBCC

'Calculate the balance. Pulls the balance off the form and subtracts it from the maximum balance (10,000) to create a balance remaining.
intMaxBalance = 10000
intBalance = intMaxBalance - Forms!frmNew_Contribution.txtBalance

' Set the Subject, Body, and Importance of the message.
.Subject = "Matching Gifts Confirmation"

The body combines strings of text with references to the fields on the form.

.Body = Forms!frmNew_Contribution.Employee.Column(3) & " " & Forms!frmNew_Contribution.Employee.Column(2) & "," & vbNewLine & vbNewLine _
& "Nuveen has matched your contribution to " & Forms!frmNew_Contribution.Organization.Column(1) & _
" for the amount of " & FormatCurrency(Forms!frmNew_Contribution.Contribution_Amount) & "." & vbNewLine & vbNewLine _
& "It was sent on " & Forms!frmNew_Contribution.Contribution_Date & "." & vbNewLine & vbNewLine & _
"Your balance available to be matched for the year is: " & FormatCurrency(intBalance) & vbNewLine & vbNewLine _
& "Please contact CONTACT INFO with any questions." _
& vbCrLf & vbCrLf

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Display The above code is all standard. I use .Display to open the email before sending so I can verify it myself. The above code does not differentiate between addressbook entries with the same last name, so I need to choose in those cases. Use .Send if you want it to send automatically.

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

 

[Kraj]

I guess I jumped the gun a little. My code is returning bad balances. It seems no not get the right balance unless I completely close the form and open it again. I'll poke at it and see what I can find, but if anyone has any ideas I'd sure like to hear 'em

EDIT: I think all I had to do was requery the box that calculated my balance. It seems to work (or at least is improving).