Solved

MS Access and emailzzzzzzzz

Posted on 2013-12-28
2
351 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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now