Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access and emailzzzzzzzz

Posted on 2013-12-28
2
Medium Priority
?
364 Views
Last Modified: 2013-12-28
Hi,
I've been trying for a couple of days now (on and off) to get this great code from Ron de Bruin to work, but it just won't attach the PDF file which is stored in "MY Documents" to the email. The email program (Outlook 2013) pops open, and it attaches my Signature, (later to be one from work)  but I just can't get it to attach the PDF file.

Code is attached

All help is greatly appreciated.
Option Compare Database
Option Explicit

Sub EmailBookings()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
    Dim Bkinglist As String
    Bkinglist = Environ("USERPROFILE") & "\My Documents\test.pdf"

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "<B>Hi,</B><br>" & _
              "Please review the attached PDF booking list.<br>" & _
              "Let me know if you have any queries.<br>" & _
              "<br><br><B>Thank you</B>"

    SigString = Environ("appdata") & _
                "\Microsoft\Signatures\Personal.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    On Error Resume Next

    With OutMail
        .To = "steve@joyceandstevieb.com"
        .CC = ""
        .BCC = ""
        .Subject = "Booking List"
        .HTMLBody = strbody & "<br>" & Signature
        .Attachments.Add Bkinglist
        .Display
'        .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Open in new window

0
Comment
Question by:Stephen Byrom
2 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 39743716
Do you have Windows XP?
In Windows 7 name of folder is Documents. You can try system independent code to get path to your file:
Dim WshShell As Object
Dim Bkinglist As String
Set WshShell = CreateObject("WScript.Shell")
Bkinglist = WshShell.SpecialFolders("MyDocuments") & "\test.pdf"

Open in new window

Look here for special folders:
http://msdn.microsoft.com/en-us/library/0ea7b5xe.aspx
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39743728
Bloody ell,
Simple as that!!
I've been scratching my head for the past two days because of two letters!
I'm running Win7 so it's just "Documents" and not "MY" ..
Old habits die hard eh?

Thanks so much for your time
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question