Solved

MS Access and emailzzzzzzzz

Posted on 2013-12-28
2
354 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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 …

679 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