• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

MS Access and emailzzzzzzzz

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
Stephen Byrom
Asked:
Stephen Byrom
1 Solution
 
als315Commented:
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
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now