Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Sending Email From Access as PDF Attachment- Please Help

    I really hope someone could help me, I know many people including myself have asked for this before but to date I have not managed to get what I want. I am not very good with writing codes but do no know how to use acces forms and reports etc.
    I curently use the following code which basically opens the report and I then print/save it as a PDF using a program called DeskPDF, as soon as it prints as pdf this program has an option to Tick to send the file as an attachment. So After it opens the PDF as an attachment in outlook, I then write Email address and the message manually and click on Send.

    However, I would like my code (below) that as soon as I click my Command Button it should send the Report as a PDF attachment, also, this Command Button should also Get the recepients E-mail which is on the same form, it should also get DateOfJob and TimeOfJob and put this in the Subject of my Email. Plus a standard message in the body of the email for the receipient. I was also wondering whether the standard mesasge could be written in font 12 Arial and part of the message in bold or different colour.

    Below is the code I currently use, please ignore some MsgBox codes as they are for our internal use.

    Private Sub CmdPrintTimesheet_Click()

    Dim strReportName As String
    Dim strCriteria As String

    ' If NewRecord Then
    ' MsgBox "You must complete AND SAVE your entry first before doing anything else." _
    , vbInformation, "Invalid Action"

    Exit Sub
    Else
    strReportName = "Booking7a"
    strCriteria = "[id]=" & Me.id

    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End If

    If HealthSafetyHazzards.Value = 0 Or IsNull(HealthSafetyHazzards.Value) Then
    Form_MyFormName.IsConfirmedBooking.Value = 0
    MsgBox ("You MUST NOT Send Timesheet Without Including The Health & Safety Information "), _
    vbExclamation, _
    "HealthSafetyHazzards"

    End If
    End Sub
    I would be most grateful for any help on this please.

    Emie
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Take a look at Peter's Software - Send an e-mail message from MS Access. The good news is that its extremely easy to do, once Outlook is properly configured (BTW this works for MS Access 2010). The bad news is that Outlook will force you to click on it before releasing the emails. Better option: the CDO library.

    Regards

    John S
    Aylmer, QC

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi John,

    Thank you for your response.
    However, I use outlook 2002 and I do not mind clicking in outlook to send email. Any more help?
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Think it works in Access 2000. Try it. Keep in mind it has many limitations. For example, the email message can only be 255 characters long! Again, your other option is the CDO library (which goes around Outlook, and does exist in Access 2000).

  5. #5
    Join Date
    May 2006
    Posts
    386
    Hi Praxis,

    I have tried all options and neither worked. I ideally want the option where you can send the report as an email attachment wether in PDF or HTML. The codes suggested by Peter's Software - Send an e-mail message from MS Access. did not work. I would be most grateful maybe if you could confirm the whole code for this. My report name is Booking7. An I want the report for each record i.e. in one customers account we have 100 bookings, so when I click on the email report, it should only email the report that one record only (based on Booking ID).

    Thank you very much in advance.
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Try adding after "docmd.openreport"
    docmd.sendobject acSendReport, stDocName, acFormatRTF, aEmail,,,aSubject, aMessage

    It definitely works (used it in a 2000 application) unless:

    1. You have not installed the appropriate libraries (in Access 2000, you may have to install one for Outlook in particular. I can't remember).

    2. Your connection in Outlook to the SMTP server (email server) is incorrect.

    3. You may be hitting a known bug ( Google it). I noticed references to a bug in "docmd.sendobject", "Access 2000", and "Outlook".

    BTW, always a good idea to describe any error messages you receive (or what happens when you try it).

    Regards

    John S

  7. #7
    Join Date
    May 2006
    Posts
    386
    Hi Praxis,
    Thank you for your response. I have added
    docmd.sendobject acSendReport, stDocName, acFormatRTF, aEmail,,,aSubject, aMessage
    after "docmd.openreport" However, I now get this error "Compile Error" Variable not defined. And the code breaks on the bold word
    DoCmd.SendObject acSendReport, stDocName, acFormatRTF, aEmail, , , aSubject, aMessage
    Below is my full code for this:
    Private Sub CmdPrintTimesheet_Click()

    Dim strReportName As String
    Dim strCriteria As String

    ' If NewRecord Then
    ' MsgBox "You must complete AND SAVE your entry first before doing anything else." _
    , vbInformation, "Invalid Action"

    Exit Sub
    Else
    strReportName = "Booking7a"
    strCriteria = "[id]=" & Me.id

    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
    DoCmd.SendObject acSendReport, stDocName, acFormatRTF, aEmail, , , aSubject, aMessage

    End If

    If HealthSafetyHazzards.Value = 0 Or IsNull(HealthSafetyHazzards.Value) Then
    Form_MyFormName.IsConfirmedBooking.Value = 0
    MsgBox ("You MUST NOT Send Timesheet Without Including The Health & Safety Information "), _
    vbExclamation, _
    "HealthSafetyHazzards"

    End If
    End Sub
    I hope you or some other friends could help please.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Change "stDocName" to "StReportName" (your String variable).

  9. #9
    Join Date
    May 2006
    Posts
    386
    Hi Praxis, I did change StDocName to "StReportName" and I get the same error.

    I hope you can help.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    You must define the variables aEmail, aMessage, and a Subject! ie.

    dim aEmail as string
    aEmail = "john@google.com" (for example)

    Same with "amessage" and "asubject". These are all string variables that you must define! Other option: replace "aEmail" (etc) with "john@google.com", "Test Message", "This is a test message".

    Regards

    J. Smith
    Aylmer, QC.

    Canada

  11. #11
    Join Date
    May 2006
    Posts
    386
    I am very sorry Praxis, but would you be kind enough to write exactly where and how these variables should be defined in the code as I am not quite sure how to do this?
    Emi-UK
    Love begets Love, Help Begets Help

  12. #12
    Join Date
    May 2006
    Posts
    386
    I am not sure if this is correct but this is what I have written, please be kind enough to amend anything that needs amending in the code as I am still getting the same problem which and the error breaks on StReportName saying veribale not defined.

    Private Sub CmdPrintTimesheet_Click()

    Dim strReportName As String
    Dim strCriteria As String

    ' If NewRecord Then
    ' MsgBox "You must complete AND SAVE your entry first before doing anything else." _
    , vbInformation, "Invalid Action"

    Exit Sub
    Else
    strReportName = "Booking7a"
    strCriteria = "[id]=" & Me.id

    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    DoCmd.SendObject _
    acSendReport, _
    "Booking7", _
    acFormatRTF, _
    "emal@emal.com", _
    , _
    , _
    "This is a test to go in the Subject field", _
    "This is a test for the actual Message to go in the body of my email", _
    False
    DoCmd.SendObject acSendReport, stReportName, acFormatRTF, aEmail, , , aSubject, aMessage

    End If

    If HealthSafetyHazzards.Value = 0 Or IsNull(HealthSafetyHazzards.Value) Then
    Form_MyFormName.IsConfirmedBooking.Value = 0
    MsgBox ("You MUST NOT Send Timesheet Without Including The Health & Safety Information "), _
    vbExclamation, _
    "HealthSafetyHazzards"

    End If
    End Sub
    Emi-UK
    Love begets Love, Help Begets Help

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what do you think that error message is telling you?
    usually it means you are using a variable which hasn't been declared


    its shoudl be pretty easy to spot
    whne the code halts and switches to the code pane you should be able to examine all variables. Im pretty certain if you move your mouse pointer over each bit of code it wiull show you what is in each variable
    failing that you can see (and set) varaible
    ?myvariable
    ..name shows you whats in that variable (myvariable)
    myvariable = "blah-di-blah"
    ...sets the varaible (myvariable) to blah di blah

    after 340_ posts I reckon its about time you refined your debugging skills
    Google
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2006
    Posts
    386
    Hi Healdem, Thank you for your email, but please come easy on me as I am not as good with codes as you or other friends, I am a newbie in this and am trying my utmost to fix this without bothering others but I am hoping that someone could help me with this particular issue as I cannot work this out.
    Emi-UK
    Love begets Love, Help Begets Help

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I repeat: so what do you think the error message is telling you?
    having read the message examine your code and see if you can spot what is wrong

    Im not trying to be "hard" on you, Im trying to push you down an avenue where you can diagnose problems with your own code yourself.. Im trying to point you down to using the tools that Microsoft supply as part of the IDE.. the debugger in VBA/Access is very very good. it can show you what the value of various things are and allow you to fix and continue debugging
    you can move the next satatemtn to be processed ()so you cna alter the code, rterun the function and so on.

    to be honest your current problem is pretty straight forward to fix, you just need to see what it is. bear in mind that the human eye can be very deceptive by the time the image has reached the brain your brain may not read what is actually there, especially if you are expecting specific words

    if you can't spot it, then re read your code, re read the message and try to spot any inconsistencies.
    if you still can't see the fault, then remove the option explicit and rerun the code.
    Last edited by healdem; 04-19-12 at 08:58.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •