Solved

Email automation problem

Posted on 2004-08-03
4
1,677 Views
Last Modified: 2012-06-21
I am trying to automate a command button in a user form to send two reports in the On Click event.  The first report is a txt file created by other software that will write a text file to My Documents.  The second report is an Access report created when the user inputs some data.  I am getting a compile error "Expected End Sub" when I run this event.

Private Sub cmdTest_Click()
    Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment

          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Alan D")
              objOutlookRecip.Type = olTo

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "This is an Automation test with Microsoft Outlook"
             .Body = "This is the body of the message." & vbCrLf & vbCrLf
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add("C:\MyDocuments\EmailAutomationCodeX.txt")
                 Set objOutlookAttach = .Attachments.Add("C:\mdb\Report")
             End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
      End Sub
End Sub
0
Comment
Question by:altosax
4 Comments
 
LVL 9

Assisted Solution

by:samopal
samopal earned 250 total points
ID: 11711076
Private Sub cmdTest_Click()
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment

          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Alan D")
              objOutlookRecip.Type = olTo

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "This is an Automation test with Microsoft Outlook"
             .Body = "This is the body of the message." & vbCrLf & vbCrLf
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
                 Set objOutlookAttach = .Attachments.Add("C:\MyDocuments\EmailAutomationCodeX.txt")
                 Set objOutlookAttach = .Attachments.Add("C:\mdb\Report")

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
                 .Save
                 .Send
          End With
          Set objOutlook = Nothing
End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11711085

try this

Private Sub cmdTest_Click()
SendMessage "AttachmentPath"

End Sub

    Sub SendMessage(Optional AttachmentPath)
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment

          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("Alan D")
              objOutlookRecip.Type = olTo

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "This is an Automation test with Microsoft Outlook"
             .Body = "This is the body of the message." & vbCrLf & vbCrLf
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             If Not IsMissing(AttachmentPath) Then
                 Set objOutlookAttach = .Attachments.Add("C:\MyDocuments\EmailAutomationCodeX.txt")
                 Set objOutlookAttach = .Attachments.Add("C:\mdb\Report")
             End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
      End Sub
0
 

Author Comment

by:altosax
ID: 11711141
samopal,

I tried that and get a Compile error: "User-defined type not defined"
0
 
LVL 34

Accepted Solution

by:
flavo earned 250 total points
ID: 11711299
>> User-defined type not defined"

Add a refrence to MS Outlook ?.? - In vb window Tools - Refrences and tick it.

Dave
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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