Access '03 send email to users in a table each their own pdf.

[DougP]

I looked here and found this thread but there was no answer.
thread707-591135

Anyway I have a table with names, emails and the pdf file name. which are all in the same folder.
I want to create 1 email for each person (record) and attach the correct pdf to it.
Can this be done?

I have used docmd.sendobject a lot. but it seems to want to send only Access objects.

here is my code so far

    Dim subject, Email, message As String
    Dim ChildLName, ChildFName, ParentName As String
    Dim pdfFile As String
    ChildLName = Forms![frmEmailParentsform]![Data from pdf Latest subform1].Form![Childs Last Name]
    ChildFName = Forms![frmEmailParentsform]![Data from pdf Latest subform1].Form![Childs First Name]
    ParentName = Forms![frmEmailParentsform]![Data from pdf Latest subform1].Form![Childs First Name]
    pdfFile = Forms![frmEmailParentsform]![Data from pdf Latest subform1].Form![PDFAdmissionFile]
    
    subject = "2012-13 Student Admission form(s)"
    message = "To the Parents of " & ChildFName & " " & ChildLName & vbCrLf
   
    message = message & "This is the new 2012-13 Student form with all of the information which was put on last years form." & vbCrLf
    message = message & "When everything is correct then send them back to me." & vbCrLf

    Email = Forms![frmEmailParentsform]![Data from pdf Latest subform1].Form![EMail Address]
    
    'this launches Outlook but then I have to manually go attach the file
DoCmd.SendObject acSendNoObject, "", acFormatTXT, Email, , , subject, message

DougP

 

[UnsolvedCoding]

Yes you can send attachments with VBA.

Start with this thread which works with most VBA apps

http://tek-tips.com/viewthread.cfm?qid=1667608

And then insert this into the with itm statment

' This gives a path to a file name that is attached
.Attachments.Add "C:\Mypath\My_Document.PDF"

When the email is created the time will attach.

These should help you solve or answer your problems as all you will need to do is plug in the variables and can include the code as a module.

 

[UnsolvedCoding]

Sorry, that should read when the email is created the item will attach.

 

[DougP]

Thanks that worked great.
Is there a way to see all the "dot" options for
Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
' Show the user the email that was just created
.Display
End With

such as .send or .save
the intellisense is not working
someone showed me along time ago how to set something to an object and it printed out all the .dot options?

something like this pseudo code

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
for each item in ???
debug.print .dot thingy ?????
next
End With


DougP

 

[Andrzejek]

If you want to have the intellisense with your Outlook object, try early binding instead, something like:

Tools - References... - check the reference to Outlook object, and then in your code you can do:

Dim AppOutlook As Outlook.Application

Set AppOutlook = New Outlook.Application
...

Code not tested

Have fun.

---- Andy

 

[UnsolvedCoding]

I don't know all of the "dot" options and haven't used Andrzejek way. The ones I use are below -

' Put who the email is going to in the to line. I suspect .BCC will work but have never had to use it.
.To
.CC

' Put the subject line info in the subject line
.Subject

' Insert the message into the email
.Body or .HTMLBody

' Show the user the email that was just created
.Display

' This gives a path to a file name that is attached
.Attachments.Add

' Save email as draft in Outlook draft folder
.Save

' Send the email
.Send

' Close the email without a popup box
.Close False

Trying to populate the from field for email is a royal pain. The only way I have found so far to populate the from field is to copy the email address using send keys then open a new email.

Make sure that the from field is always selected for new email, so far I haven't had consitant results with opening it once a new email is created.

Then using send keys back tab from the body of the message into the from field and put in the from email address with a paste - again using send keys. And if there isn't a long enough pause after the paste - in some cases up to 7 or 8 seconds, the from field won't populate. Also be aware that you may need to wait for a second or longer after each back tab to make sure you are in the proper field before going on.

Very icky.

 

[PHV]

populate the from field for email is a royal pain
Have a look at the SentOnBehalfOfName property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

PHV - acutally we tested sent on behalf of and it failed miserably. This is why:

One project, due to rotation of work scheduleds, required the persons name that was responsible for the particalur task for that day to be displayed and the email to be sent from a shared in box.

Since there were about 8 different shared email addresses that about 30 people could use, sent on behalf of created a lot of misplaced emails because clients would click reply instead of responding to the correct person.

Since everyone was in the same department but on different teams and there were hundreds of emails a day, it simply overloaded one of the inboxes and the others sat empty.

Sent on behalf shows who sends the email and the person its sent for, instead of just the person its being sent for, and the only way to stop getting slammed with response emails as the actual sender was to do a from so the emails went to the person doing the task for the day and not back to the original sender.

Using sent on behalf of resulted in litterally hundreds of emails beings sent to the wrong person - typically the IT person running the app - instead of it going to the person that it was supposed to go to for the day because of clients clicking reply.

The resulting mess was not fun.

The only way around it was to use the from field and not sent on behalf of.

 

[PHV]

And did you try the NameSpace.Logon method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

As I recall it was also explored.

An archived version will have to be pulled to cross check the notes on what happened.