Open email app. and attach a file using VBA

steven.darby

Registered User.
Local time
Today, 15:18
Joined
Dec 21, 2009
Messages
10
Open Outlook email app. and attach a file using VBA OFFICE07

HI, hoping someone has something relatively simple, or can direct me to the right place, I want to open Outlook email application on click and attach a document (I have the full file path and file extension)

To date I've only ever used Docmd.sendobject <access report>, can I use this to send a document. My db isnt using actual attachments, I am just storing filepaths in a table. I guess then, next question is do I need to save the original document to the database first as some sort of temporary attachment perhaps?

I'm not interested in actually sending the email, or closing the app after, just to open if not already, add to: & subject: and attach a doc.

- - - - - - -

I've tracked down some code from the forums but I get error "user type not defined " on first line ... Dim Myoutlook as outlook.application

not a very good start I know, I am however using access 07 in case this is anything to do with, any help much appreciated

>> NOW FIXED ! -- In VBA tools, references menu I didnt have Microsoft Outlook 12.0 Object Library set, just checked the box and now all working fine! Code insert...

Code:
Public Function Email_now()

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim objOutlook As Object
Dim Attach As String

'Check if outlook if open
Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If objOutlook Is Nothing Then
 ' Enter code to open outlook...
    'still working on this !!
    End If

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
      
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = "test" '**put in reference to form

'MyMail.CC = MailList("Copy To")
'This gives it a subject
MyMail.Subject = "test" '**put in reference for subject
'This gives it the body
MyMail.Body = "test"
'If you want to send an attachment
'uncomment the following line
Attach = "C:\users\home_steven\test.txt"  '** add file path to attach doc
MyMail.Attachments.Add Attach

'This sends it!
'MyMail.Send
'To Display instead of Send, Uncomment the next line
'And comment the "MyMail.Send" line above this.
MyMail.Display

'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing
End function
 
Last edited:
I find it better to use late binding. This way you do not have to set the reference to Outlook (early binding). It will also allow the code to work with whatever version of Outlook is installed without having to change the reference.

See:
Late Binding in Microsoft Access
 
Thanks, I had seen some notes on late and early binding but I certainly aint no expert in VBA or automation, generally I take snippets from different places stick them together and hash to do what I want to do, mostly in the end I understand how it works, like my code above (after a few hours!), I understand the principle of what your telling me, but I've no idea how actually to do it!

Your link I dont really understand, other links talk about Set ObjOutlook = CreateObject ("Outlook.application") with delcarations just as Dim ObjOutlook, but I tried this first and it didnt work (of course I didnt know I as attempting late binding at the time), if you can help jig my code above to late binding method, then I'd give it a go, many thanks
 
aahh

but your code is very similar to some stuff I found and use

to fix your problem, you probably just need to set a reference to the outlook library

open a module, and then click tools/references

find the one for microsoft outlook, and set it

then your code should work.
 
Thanks Gemma, done that and it works, thanks. Buy actually I was asking hitechcoach for late bindings method, which doesnt require to set to outlook reference, which means in future when IT install new version of outlook, it will still work, otherwise I have to go back and change the outlook reference.

thanks anyway
 
yeah, i never quite understand the difference - or more truthfully, have never needed to - but an app i am developing has exactly this problem

my outlook library is version 12

the clients is version 11 - most references auto-correct - but this one doesnt seem to, so i have ot change it manually each time
 
Access will upgrade a reference to a newer version since they are normally backwardly compatible.

Access will NOT downgrade to an earlier version due to possible compatibility issues.

While developing, I will use early biding so that I can use the intelli-sense help.

After testing and before creating the MDE/ACCDE, I will switch back to late binding and remove the reference.

I will put together an example for you.
 
Your code using early binding

Code:
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

to switch to late binding use:

Code:
Dim MyOutlook As Object   ' <<< Outlook.Application to Object 
Dim MyMail As Object   ' <<<  Outlook.MailItem to Object


' Now, we open Outlook for our own device..
Set MyOutlook = CreateObject("Outlook.Application") ' >>> was = New Outlook.Application
 
Your code using early binding

Code:
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application

to switch to late binding use:

Code:
Dim MyOutlook As Object   ' <<< Outlook.Application to Object 
Dim MyMail As Object   ' <<<  Outlook.MailItem to Object


' Now, we open Outlook for our own device..
Set MyOutlook = CreateObject("Outlook.Application") ' >>> was = New Outlook.Application

thanks

i'll change my code and see what happens

will this compile on machines without the relevant library. I'm thinking of mappoint.
 
Code:
Sub TestEMail()

    SendEMail "who@where.net.au", _
              "", _
              "", _
              "C:\db2.zip", _
              "C:\AUTOEXEC.BAT", _
              "C:\boot.ini"
              
End Sub


Public Sub SendEMail(ByVal strTo As String, _
                     ByVal strSubject As String, _
                     ByVal strBody As String, _
                     ParamArray vntAttachments() As Variant)
                     
    Dim intIndex As Integer
    
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = strTo
        .Subject = IIf(Len(strSubject), strSubject, "No subject transmitted.")
        .Body = IIf(Len(strBody), strBody, "No body transmitted.")
        For intIndex = LBound(vntAttachments) To UBound(vntAttachments)
            .Attachments.Add vntAttachments(intIndex)
        Next intIndex
        .Display
    End With
    
End Sub
 
thanks

i'll change my code and see what happens

will this compile on machines without the relevant library. I'm thinking of mappoint.

Yes, it will compile.

With early binding, as soon as you open a database, it looks for all the set references. If any are missing, then you have lots of issues.

With late binding this does not happen. Acess does not attempt to locate the library until the code actually tries to execute!

I like to use VBA code to test for the existing of software before I actually allow the code that uses late binding to run.
 
Hi,

Sorry for bringing up an old subject. I am trying to use the code posted here on my 2007 desktop at home however, I am developing a utility for use with access 2003 at work.

The code in it's current form on my access 2007 pc doesn't send the email if outlook is not running, will it be the same for when I implement the utility on my works' pc's?

I think this part is the problem.

Code:
' Enter code to open outlook...
    'still working on this !!
    End If
 
Hi,

Sorry for bringing up an old subject. I am trying to use the code posted here on my 2007 desktop at home however, I am developing a utility for use with access 2003 at work.

The code in it's current form on my access 2007 pc doesn't send the email if outlook is not running, will it be the same for when I implement the utility on my works' pc's?

I think this part is the problem.

Code:
' Enter code to open outlook...
    'still working on this !!
    End If

Check out the code from this previous post
 
Last edited:
Thanks HiTechCoach. That is exactly what I did, just before I read your reply.

:)
 

Users who are viewing this thread

Back
Top Bottom