Solved

Email automation problem

Posted on 2004-08-03
4
1,701 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
[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
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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