Email different reports to different emails with one click

[bass_twitch]

Hi all

I'm still very new to the Access/VBA scene, but I have managed to copy,adjust and fumble my way through the creation of some smarter forms/reports in this database.
However I have just been assigned to create a 'magical email button'.

Ultimately what I am looking to do is this:
On the click of a button run a VBA process that:
- emails all the project managers their relevent individual project report in pdf form.

Some Background:
- The database is currently able to run a project specific report based on a combo box on a previous form. However to change the project the report is based on the report has to be closed and the combo box on the previous form changed, and the report reopened.
- I currently have a table and query of the project manager emails and their relevant projectID (which is what links the form and report together).

Any help or suggestions or guidance would be muchly appreciated!! Or if you need any more information please let me know.
Thanks!

 

[Mr. B]

I'm still very new to the Access/VBA scene

Not knowing just how much VBA experience you have, makes it a little difficult to provide you with specific help.

Ultimately what I am looking to do is this:
On the click of a button run a VBA process that:
- emails all the project managers their relevent individual project report in pdf form.

To get started on a project like this, you first need to define over all goal of the project which you seen to have started. Get specific with your over all goal: For example:
Use VBA to iterate through a list of Project Managers and their Project and send a PDF version of a report to each manager about each of their projects.

As you then start to look at the over all project, you will start to come up with questions that need to be addressed. For example, some questions that came to my mind were, "Does each manager have multiple projects?; If each manager has multiple projects, does the report already provide information to each manager about each of his projects?

Next, you can start to identify as many elements required for completing your project and start to map out the solutions to each of the elements you need to use to accomplish your over all goal. For example:
1. In VBA, create a record set of the Managers and their email addresses.
2. Use a loop to iterate through the managers record set
a. Create an sql statement to update the QueryDef of the existing query
b. Create a PDF version of the existing report, saving it to a specific location
c. Create an email addressed to manager and attach the new PDF document
d. Determine the work around to use to automating the sending of the email
3. Notify the user when all emails have been created and sent

These are just some of the things that I could map out just looking at this very quickly. Because you know your situation much more that anyone else, you will be able to expand on scoping your project.

Then it is a simple matter of starting to create the processes. As you start to create each process, you may find that you need to break each step down ever further, but at least then you can come back to the forums and start by searching for existing information about specific needs, or asking question when you can't find what you need.

Hope this helps get you kick started in the right direction.

 

[bass_twitch]

Not knowing just how much VBA experience you have, makes it a little difficult to provide you with specific help.



To get started on a project like this, you first need to define over all goal of the project which you seen to have started. Get specific with your over all goal: For example:
Use VBA to iterate through a list of Project Managers and their Project and send a PDF version of a report to each manager about each of their projects.

As you then start to look at the over all project, you will start to come up with questions that need to be addressed. For example, some questions that came to my mind were, "Does each manager have multiple projects?; If each manager has multiple projects, does the report already provide information to each manager about each of his projects?

Next, you can start to identify as many elements required for completing your project and start to map out the solutions to each of the elements you need to use to accomplish your over all goal. For example:
1. In VBA, create a record set of the Managers and their email addresses.
2. Use a loop to iterate through the managers record set
a. Create an sql statement to update the QueryDef of the existing query
b. Create a PDF version of the existing report, saving it to a specific location
c. Create an email addressed to manager and attach the new PDF document
d. Determine the work around to use to automating the sending of the email
3. Notify the user when all emails have been created and sent

These are just some of the things that I could map out just looking at this very quickly. Because you know your situation much more that anyone else, you will be able to expand on scoping your project.

Then it is a simple matter of starting to create the processes. As you start to create each process, you may find that you need to break each step down ever further, but at least then you can come back to the forums and start by searching for existing information about specific needs, or asking question when you can't find what you need.

Hope this helps get you kick started in the right direction.

Hi mate

Thank you for your reply
It is good to know I am on the right track with it.
I have been giving this a bit of thought, and this is the way I was planning on attacking it:
1) on the click of a button it runs a loop that:
a) replaces the first/next projectID into the cboID on the generator form
b) emails the report using the send.object cmd in pdf format to the project manager's email (contained in a subform on the generator form)
c)loops until all projects IDs are done.

I have thought about the fact that some managers have more than one project, but I am fine with them getting more than one email (might help keep them seperate).

The parts that I understand how to do are:
- run the vba from a button
- use the send.object cmd

I am unsure how to create a loop that replaces the cboIDs until finished.. any help you can provide would be much appreciated..

Thanks

 

[bass_twitch]

bump? .

 

[pbaldy]

See if this and the related link on loops get you started:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[bass_twitch]

I am trying to reply, but because I have <10 posts it is not letting me post (it says I have email addresses in my post.. even though I do not. I only have the words 'email address'

 

[bass_twitch]

Thanks to the link that was posted above (thanks )
I've managed to use that to create a process, and it is working to an extent.... HOWEVER It is sending the email to the previous record's email address!...
Help??
Essentially it is not updating the records before it tries to send the report. I have tried placing the updater in several spots ( and even tried having it in 3 places at once) but still to no avail, is there anyway of making sure the code does the update of subforms, Before it tries to send the email?

Thanks

 

[bass_twitch]

i.e. is there a cmd that says: wait until [blahblah] cmd is finished, then proceed?

 

[pbaldy]

Not having seen your code, it's hard to say what might be going wrong. You can force a record to save with:

If Me.Dirty Then Me.Dirty = False

 

[bass_twitch]

Thanks mate

I jus tried dumping that in to no avail
I might just spam a couple more posts, so I can then post my code

 

[bass_twitch]

11111111111

 

[bass_twitch]

222222222222

 

[bass_twitch]

333333333333333

 

[bass_twitch]

Ok so here is my code:

Private Sub IDcycle3()
'On Local Error GoTo Some_Err

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long

DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Me!txtProgress = Null
Set RS = MyDB.OpenRecordset _
("qry_CapFilter")
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No email messages to send.", vbInformation
Else
RS.MoveLast
RS.MoveFirst
If Me.Dirty Then Me.Dirty = False
Call FilterButton_Click
Do Until RS.EOF
lngCount = lngCount + 1
'lblStatus.Caption = "Writing Message " & CStr(lngCount) _
& " of " & CStr(lngRSCount) & "..."
Call FilterButton_Click
strID = RS!ProjectId
[Forms]![frm_EvmReport]![cboID] = strID
DoCmd.Requery "subfrm_Pname"

strTo = [Forms]![frm_EvmReport]![subfrm_PName].[Form]![EmailAddress]
'intMessageID = Year(Now) & Month(Now) & Day(Now) & Fix(Timer)
' Send the email using some technique or other
DoCmd.SendObject acSendReport, "rpt_EvmOverview", acFormatPDF, strTo, , , "MonthlyEVMReport" & " " & Month(Now) - 1 & "-" & Year(Now), "Hi Project Manager. Please find attached your monthly EVM Report. Please note that this is an automated email.", False
RS.Edit
'RS("cpeDateTimeEmailed") = Now()
RS.Update
RS.MoveNext
Call FilterButton_Click
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close

'Me!txtProgress = "Sent " & CStr(lngRSCount) & " emails."
'lblStatus.Caption = "Email disconnected"
'MsgBox "Done sending Promo email. ", vbInformation, "Done"
'lblStatus.Caption = "Idle..."
Exit Sub

'Some_Err:
'MousePointer = 0
'MsgBox "Error (" & CStr(Err.Number) & ") " & Err.Description, _
vbExclamation, "Error!"
'lblStatus.Caption = "Email disconnected"
End Sub

And the code for the updater: FilterButton_Click is:


Private Sub FilterButton_Click()
Dim strWhere As String
Dim lngLen As Long
DoEvents


If Not IsNull(Me.cboID) Then 'Number field example.
strWhere = strWhere & "([ProjectID] = " & Me.cboID & ") AND "
End If
If Not IsNull(Me.cboYr) Then 'Number field example.
strWhere = strWhere & "([Yr] = " & Me.cboYr & ") AND "
End If


lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
Dim RS As Recordset

Set RS = Me.subfrm_EVM_monthly_data.Form.RecordsetClone
On Error Resume Next
RS.MoveLast
On Error Resume Next

Me.subfrm_EVM_monthly_data.Form.Bookmark = RS.Bookmark

 

[bass_twitch]

bumpity bump?

 

[pbaldy]

What exactly is the problem?

 

[bass_twitch]

Thanks to the link that was posted above (thanks )
It is sending the email to the previous record's email address!...
Help??
Essentially it is not updating the records before it tries to send the report. I have tried placing the updater in several spots ( and even tried having it in 3 places at once) but still to no avail, is there anyway of making sure the code does the update of subforms, Before it tries to send the email?

Thanks

As above .

 

[pbaldy]

I would expect to get the email address from the recordset, not the form. Should it be coming from there?

 

[bass_twitch]

I would expect to get the email address from the recordset, not the form. Should it be coming from there?

Hmm it wasn't, but now it is and it is working beautifully
Thanks mate

 

[pbaldy]

Happy to help.