Solved

MS Access and emailzzzzzzzz

Posted on 2013-12-28
2
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 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