Sending Individual Reports To Individual Emails

MarkyC,

Thanks for responding to my distress call. Thanks for posting the detailed steps that you took. I seem to see the light at the end of the tunnel now. I will try all these things over the weekend when I have a lot of free time to experiment.

I really appreciate you taking all this pain to write all these steps. That is exactly what I was looking for. If I am stuck, I will catch you on this thread again.

Thanks to Paul also for responding. Paul you have really no idea how much effort I am putting into exploring things but that's alright. I am not offended. You just mistook me for an expert and thought I would start running marathon whereas I am still learning how to walk first.

Catch you guys next time to tell whether I was able to figure this out or not.
 
MarkyC,

I don't think I thanked you enough in my last post above so thank you once again for posting these detailed steps. I really appreciate it.
 
No problem. I just never received any email notification for the replies in the thread, for some strange reason. Maybe it's only active for a short time as I did actually receive ones for your last two posts. I got an email notification for your PM which prompted me to pop in. I'll try and keep an eye on the thread in case you need any help. As I said though, mine is a very botched job, so to speak, and I'm sure there's probably a much better and simpler way to do it through coding. I'm just not very good at it, and this way sorts my problem out anyway.

Just one point to note, the form runs on a timer every 3 seconds. Which is fine for what I need it to work with as there will less than 200 people in the table, so the process will take around 10 minutes tops (as it just sort keeps auto-refreshing as it were). If you have a bigger distribution list, just bear in mind it will take longer.
 
MarkyC,

I think you stopped getting email notifications as the system is designed to track whether or not you logged in a couple of times after posting your question or reply. The very first reply to my thread came with a disclaimer that if I don't log in again I won't get future notifications about that thread.

I think you have given me more than what I asked for. Let me try this over the weekend. I don't care how botchy or low level this might turn out to be as long as it tries to achieve the automation that I am intending to achieve. There is whole life lying ahead to improve on it.

Thanks once again and thanks for offering to keep an eye on this thread as I might need some finangling here and there soon.
 
Have to assume all is well. If anyone else wants any more information on my "bodge job" feel free to send a PM which I'll get a notification for.
 
Hey MarkyC,

I apologize for not getting back to this thread. Sorry I got so busy and could not find enough time to experiment on the procedure that you suggested.
Please be assured that I do appreciate all your help and all other folks' who tried to help.
When I try the method, I will definitely share my excitement. Until then please bear with me. And I hope to try soon.
 
similar problem for a complete newbe - Please see code below that returns a run-time error 3265 due to the line: strTo = !email_address1

however, if i changed it to strTo = !AccountNumber it will populate in the outlook email To: line properly but i need the email address, not the account number - do i need to define email_address1? it is a short text field in my query/report. if so, how do i define it? totally new to access.

Option Compare Database

Sub Mac1()
Dim rsAccountNumber As DAO.Recordset
Dim strTo As Variant
Dim strSubject As String
Dim strMessageText As String

Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT AccountNumber FROM P3_DVP_UnAffirmed_Report_for_En Query", dbOpenSnapshot)

With rsAccountNumber

Do Until .EOF

DoCmd.OpenReport "Unaffirmed Report", _
acViewPreview, _
WhereCondition:="AccountNumber = '" & !AccountNumber & "'", _
WindowMode:=acHidden

strTo = !Email_Address1
strSubject = "Invoice Number "
strMessageText = "Text Here"


DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="Unaffirmed Report", _
OutputFormat:=acFormatPDF, _
To:=strTo, _
Subject:=strSubject, _
MESSAGETEXT:=strMessageText, _
EditMessage:=True


DoCmd.Close acReport, "Unaffirmed Report", acSaveNo


.MoveNext

Loop

.Close

End With
End Sub
 
You haven't included Email_Address1 in the recordset, so it's not available to your code.
 
As you say you are totally new to access, you will want to change your select statement
Code: 
Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT AccountNumber FROM P3_DVP_UnAffirmed_Report_for_En Query", dbOpenSnapshot)
to include the EMail address as Paul indicated.
Code: 
Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT AccountNumber, Email_Address1 FROM P3_DVP_UnAffirmed_Report_for_En Query", dbOpenSnapshot)

You will also want to review your naming conventions and data normalization. If you have "Email_Address1", that would indicate you have more than one Email address in your table. This is a poor choice as you have much more work to do when you are trying to determine what Email address to send to. I would put it in a child table.
 
thanks - i copy pasted your reply directly into the module but it breaks at the line with a 3061 error: too few parameters Expected 1 ...Email_Address1 is a field in my query/report but i wont need to use the other email addresses, so i would think i'd be fine from that standpoint. thx.
 
Is there actually a space before Query? That object name would need to be bracketed with square brackets if so. Spaces shouldn't be used in object names; they're more trouble than they're worth. That error implies something is spelled wrong if you still get it.
 
thx - i now get through that line & the PDF generates in an email to send out, but my To: line (for the recipient is blank) it should be: strTo = ![Eml Add1] - i am adding my updated code for all to see the difference that allowed me to get passed the original break:

Option Compare Database

Sub Mac1()
Dim rsAccountNumber As DAO.Recordset
Dim strTo As Variant
Dim strSubject As String
Dim strMessageText As String

Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT AccountNumber, [Eml Add1] FROM [P3_DVP_UnAffirmed_Report_for_En Query]", dbOpenSnapshot)

With rsAccountNumber

Do Until .EOF

DoCmd.OpenReport "Unaffirmed Report", _
acViewPreview, _
WhereCondition:="AccountNumber = '" & !AccountNumber & "'", _
WindowMode:=acHidden

strTo = ![Eml Add1]
strSubject = "Invoice Number "
strMessageText = "Text Here"


DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="Unaffirmed Report", _
OutputFormat:=acFormatPDF, _
To:=strTo, _
Subject:=strSubject, _
MESSAGETEXT:=strMessageText, _
EditMessage:=True


DoCmd.Close acReport, "Unaffirmed Report", acSaveNo


.MoveNext

Loop

.Close

End With
End Sub

NOTE: for purposes of this post i had to change the name to Eml Add1 since i havent built up enough posts yet
 
hmm, did i do this right? i popped it in here:
Sub Mac1()
Dim rsAccountNumber As DAO.Recordset
Dim strTo As Variant
Dim strSubject As String
Dim strMessageText As String

Set rsAccountNumber = CurrentDb.OpenRecordset("SELECT DISTINCT AccountNumber, [Eml Add1] FROM [P3_DVP_UnAffirmed_Report_for_En Query]", dbOpenSnapshot)

Debug.Print ![Eml Add1]

but now i get an Invalid or Unqualified Reference error

all other code remains the same
 
when i re-run it using:
Debug.Print strTo instead of
Debug.Print ![Eml Add1] it will run, without errors but again, the email address wont appear in the to: field, its just blank.
 
UGH! thanks for all the help guys - the first account i was referencing had the email missing, that's why it was blank!!! -
 
We've all done it; glad you got it working.
 
You must log in or register to reply here.

Similar threads

A
Solved Email Report
Replies
15
Views
459
Pat Hartman
P
F
Solved Is there a way to add multiple detail tabs to one report?
Replies
6
Views
444
Pat Hartman
P
E
Repeating Details
Replies
11
Views
631
Pat Hartman
P
Isaac
Outlook Android "Note to Self" rant
Replies
17
Views
419
Gasman
Gasman
J
Local group [club] membership renewal by email, not post?
2 3 4
Replies
73
Views
2,163
John O'Malvern
J

Users who are viewing this thread

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