Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10

    Command Button to Send Outlook Email

    Hello all - I'm a relatively new Access user. I had training last month (3 classes, basic to advanced) and I've been working on my database creation skills for a few weeks. My boss recently gave me a larger project to create a DB for and I need some help with one of the features I'm building in.

    I work for a doctor, and the database is to assist with surgery scheduling. I have a form in the database with a large number of fields. One field is a combo box which pulls from a table with medical device reps' contact information. When you select the device name, four other fields populate the rep name, company name, rep phone number, and rep email address. Next to this, I have placed a command button.

    I want the user to be able to click the command button, which will open Outlook (if not already open, which it usually is), create a new mail message, and pre-populate that particular rep's email address in the To: field, plus some kind of general subject like "RWP Office Scheduling Notification" in the Subject: line.

    I've tried searching a number of forums to get an answer for this, but I can't find any kind of code or macro that I could modify to fit my particular use case. Bear in mind that my VBA skills are basic, but I'm really good at copy-and-paste .

    Can anyone please help me? Thanks in advance!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your case is neither unusual nor complex from the sound of it. What code have you tried that isn't working? SendObject is your simplest solution.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    Your case is neither unusual nor complex from the sound of it. What code have you tried that isn't working? SendObject is your simplest solution.
    Hey pbaldy, thanks for the reply. I copied some code that I found on another forum and attempted to modify it to fit my needs, but I don't think I really knew what I was doing. Can you please explain what you mean by SendObject being my simplest solution? VBA code is not something I have much depth in yet.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by AJM229 View Post
    Can you please explain what you mean by SendObject being my simplest solution?
    There are numerous ways to send email from Access. SendObject is probably the simplest, as it can be one line of code. Have you looked in help, or did the code you find use it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    This is the code I "stole" from online and tried to modify. I get an error message about an End Sub when I try to click my button.
    Sub TestOutlook()
    Dim appOutlook As New Outlook.Application
    Dim strEmail As String, strBody As String

    Dim objEmail As Outlook.MailItem

    Set objEmail = appOutlook.CreateItem(olMailItem)

    strEmail = Forms![frmPatientEntry]![Text285].Value

    With objEmail
    .To = strEmail
    .Subject = "New Surgery - RWP Office - Private"
    .Send
    End With
    On Error Resume Next
    If objEmail Is Nothing Then Else Set objEmail = Nothing
    'If YOu want to keep Outlook open:
    If appOutlook Is Nothing Then Else Set appOutlook = Nothing

    End Sub
    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's Outlook automation. It would certainly be a problem to have End Sub twice at the end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Thanks for that advice! I took out the second "End Sub" at the end, but my button doesn't do anything at all now.... Any other input as to the problem?

  8. #8
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Here's what it now looks like:
    Private Sub Command272_Click()
    Sub TestOutlook()
    Dim appOutlook As New Outlook.Application
    Dim strEmail As String, strBody As String

    Dim objEmail As Outlook.MailItem

    Set objEmail = appOutlook.CreateItem(olMailItem)

    strEmail = Forms![frmPatientEntry]![Text285].Value

    With objEmail
    .To = strEmail
    .Subject = "New Surgery - RWP Office - Private"
    End With
    On Error Resume Next
    If objEmail Is Nothing Then Else Set objEmail = Nothing
    'If YOu want to keep Outlook open:
    If appOutlook Is Nothing Then Else Set appOutlook = Nothing

    End Sub

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Sub TestOutlook is extraneous and should cause an exception during g compile time.

    It seems you coppiced the entire procedure and then pasted it in yours.

    You got rid of the end sub now get rid of the other line you should not have pasted.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll stay out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Sorry for treading Paul.

  12. #12
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Tread all you want, guys! This is great for me to learn, and I appreciate both of your time.

  13. #13
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Quote Originally Posted by ItsMe View Post
    Sub TestOutlook is extraneous and should cause an exception during g compile time.

    It seems you coppiced the entire procedure and then pasted it in yours.

    You got rid of the end sub now get rid of the other line you should not have pasted.
    I got an error after doing this: "Compile Error: User-defined type not defined."

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am going to guess that error is because you do not have a reference to the Microsoft Outlook Object Library or something close to that description. I will guess that if you click the "Debug" option when the error occurs, the VBA editor will open and the following line of code will be highlighted.
    Dim appOutlook As New Outlook.Application

    You cannot make a declaration that Access does not recognize. Paul mentioned in post #6 that the code you are using is interacting with Outlook. The declaration you made allows for this interaction (lines of code subsequent to your declaration is the interaction). The declaration you made requires a reference to the library I mentioned.

    There is another way without the reference to the library but, I will not go into that here.

    In order to early bind your object and instantiate your new class, you will need to make a reference. Within the VBA editor, go to Tools>References and then scroll to select "Microsoft Outlook ....."

    Tick the option and click OK.

    Debug> Compile and see if you get any further errors during compile time.

  15. #15
    AJM229 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    10
    Quote Originally Posted by ItsMe View Post
    I am going to guess that error is because you do not have a reference to the Microsoft Outlook Object Library or something close to that description. I will guess that if you click the "Debug" option when the error occurs, the VBA editor will open and the following line of code will be highlighted.
    Dim appOutlook As New Outlook.Application

    You cannot make a declaration that Access does not recognize. Paul mentioned in post #6 that the code you are using is interacting with Outlook. The declaration you made allows for this interaction (lines of code subsequent to your declaration is the interaction). The declaration you made requires a reference to the library I mentioned.

    There is another way without the reference to the library but, I will not go into that here.

    In order to early bind your object and instantiate your new class, you will need to make a reference. Within the VBA editor, go to Tools>References and then scroll to select "Microsoft Outlook ....."

    Tick the option and click OK.

    Debug> Compile and see if you get any further errors during compile time.
    The Microsoft Outlook options were already checked in my References menu, and the highlighted text was the "Private Sub Command272_Click()" line, giving me an "Expected End Sub" error. I've changed the code so it looks like this now:
    Private Sub Command272_Click()
    Dim appOutlook As New Outlook.Application
    Dim strEmail As String, strBody As String

    Dim objEmail As Outlook.MailItem

    Set objEmail = appOutlook.CreateItem(olMailItem)

    strEmail = Forms![frmPatientEntry]![Text285].Value

    With objEmail
    .To = strEmail
    .Subject = "New Surgery - RWP Office - Private"
    End With
    On Error Resume Next
    If objEmail Is Nothing Then Else Set objEmail = Nothing
    'If YOu want to keep Outlook open:
    If appOutlook Is Nothing Then Else Set appOutlook = Nothing
    End Sub
    Now nothing at all happens when I click the button. Can one of you guys please help me out here? Maybe VBA code isn't the best way to do what I want. I'm totally open to other suggestions, but I need help!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. send outlook email with late binding
    By markjkubicki in forum Programming
    Replies: 3
    Last Post: 01-24-2014, 09:39 AM
  2. Replies: 9
    Last Post: 11-27-2013, 11:08 AM
  3. Replies: 5
    Last Post: 04-25-2013, 10:36 AM
  4. Send email in Outlook with attachment
    By kelkan in forum Programming
    Replies: 1
    Last Post: 02-01-2013, 10:31 PM
  5. Send email from Access thru Outlook
    By ZMAN in forum Forms
    Replies: 2
    Last Post: 11-27-2010, 06:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums