Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136

    Send email from form based on control

    Hi,


    I have a button on my form that when clicked generates a draft outlook email with pre-filled subject, body, email recipients, etc. It works perfectly.

    However, now the users want to generate different email body/subjects based on a specific control in the form. So if P2P is selected in this control it's one email and if M2M is selected a different email is generated.

    Does anyone know the syntax to make this happen? The control iI want to base it on is Comparison_Type

    Here's what I have now:

    Code:
    Private Sub cmd_email_Click()
    
    Dim Msg As String
    
    
    Msg = "Dear " & User & ",<P>" & _
        "The " & Comparison_Type() & " comparison of " & Product() & " has been completed. Please review the notes below." & "<P>" & Comments
    
    
    
    
    Dim O As Outlook.Application
    Dim M As Outlook.MailItem
    
    
    Set O = New Outlook.Application
    Set M = O.CreateItem(olMailItem)
    
    
    With M
        .BodyFormat = olFormatHTML
        .HTMLBody = Msg
            .Subject = Comparison_Type() & " Comparison for " & Product() & " is Complete"
        .SentOnBehalfOfName = "xyz@gmail.com"
        .Display
    
    
        
    End With
    
    
    Set M = Nothing
    Set O = Nothing
    
    
    
    
    End Sub

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Put them all in a table and look them up. Do not hard code them, as they will want amendments and you will need to amend your code all the time.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Can you please help me hard code it anyway? If it's really too complicated then I can try a table. Not sure how to do that either. I got the table part of course, but how do I look it up?

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    The table woul have
    Autonumber
    Control Values
    Subject
    Body
    and whatever else you think you should have, signature perhaps?

    Anyway, then you can either use a recordset or DLookUp() to get the data to populate your email properties.

    I would use the Select Case statement if I HAD to hardcode it, for any different properties in the email per control value, and then populate any common properties of the email.

    https://learn.microsoft.com/en-us/of...case-statement
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    I'm trying to use the Select Case statement and I can't get it to work. Can you please show me what it might look like? The control is Comparison_Type and the values are P2P and M2M. Depending on which value is in the control, different body text will appear in the email.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well if it only ever 2 and always will be one or the other, just use an If and Else construct.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    I know how to write a basic If Then Else statement, but I don't know how to write it to change out the body of an email. Can you assist? Sorry, I barely know VBA.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by matey56 View Post
    I know how to write a basic If Then Else statement, but I don't know how to write it to change out the body of an email. Can you assist? Sorry, I barely know VBA.
    Well you really need to start as the If Else construct is one of the easiest.

    I would dim two string variables, strP2P and strM2M
    Then assign their values.
    Then decide which you want to use?

    Code:
    Dim strP2P As String, strM2M As String
    strP2P = "Dear " & User & ",<P>" & "The " & Comparison_Type() & " comparison of " & Product() & " has been completed. Please review the notes below." & "<P>" & Comments
    strM2M = "Dear " & User & ",<P>" & "The " & Comparison_Type() & " comparison of " & WhateverMIs() & " has been completed. Please review the notes below." & "<P>" & Comments
    
    If Comparison_Type = P2P Then
        msg = strP2P
    Else
        msg = strM2M
    EndIf
    However I am not sure what you are doing with () after these names. They are controls or fields. If controls then I would be prefixing them with Me. They are not functions?, so unsure how what you have is even working correctly?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by Welshgasman View Post
    Well you really need to start as the If Else construct is one of the easiest.

    I would dim two string variables, strP2P and strM2M
    Then assign their values.
    Then decide which you want to use?

    Code:
    Dim strP2P As String, strM2M As String
    strP2P = "Dear " & User & ",<P>" & "The " & Comparison_Type() & " comparison of " & Product() & " has been completed. Please review the notes below." & "<P>" & Comments
    strM2M = "Dear " & User & ",<P>" & "The " & Comparison_Type() & " comparison of " & WhateverMIs() & " has been completed. Please review the notes below." & "<P>" & Comments
    
    If Comparison_Type = P2P Then
        msg = strP2P
    Else
        msg = strM2M
    EndIf
    However I am not sure what you are doing with () after these names. They are controls or fields. If controls then I would be prefixing them with Me. They are not functions?, so unsure how what you have is even working correctly?
    Ok we're getting close! The () are the values of those fields in the tables. For some reason no matter which value is in the Comparison_Type field (P2P or M2M) it's giving me the custom message I wrote for the M2M (albeit with the correct Comparison_Type). Why would that be?

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Sorry, surround the values with ".
    I typed that directly into thread, not Access, my bad. :-(
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Also, that tells me you do not have Option Explicit at the top of this module, and probably others, as it would not have compiled. So you need that as well.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    You are my hero Welshgasman! It's working perfectly.

    One final question, I promise. If I want no action when the email button is clicked if the cell is empty (e.g. no record is selected - just a blank form) how do I code that? Right now it uses the Else value which is M2M and generates that email version.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well either go back to the Select Case option, or enclose that If Endif with another, which tests if a value exists or not.
    That IF Else only works if those are the only two values as I mentioned before? If you have a third which is either Null or a ZLS then that is a third option.

    Code:
    Select Case Comparison_Type
        Case "P2P"
            P2P message
        Case "M2M"
            M2M message
    End Select
    
    https://learn.microsoft.com/en-us/of...case-statement
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    I just added another option for null. Now if the value is blank it doesn't include the message which is good. However, it still opens the email when clicked. How do I stop the code below from running if the field is blank?

    Code:
    Dim O As Outlook.ApplicationDim M As Outlook.MailItem
    
    
    Set O = New Outlook.Application
    Set M = O.CreateItem(olMailItem)
    
    
    With M
        .BodyFormat = olFormatHTML
        .HTMLBody = Msg
        .Subject = Comparison_Type() & " Comparison for " & Product() & " is Complete"
        .SentOnBehalfOfName = "xyz@gmail.com"
        .Display
       
        
    End With
    
    
    Set M = Nothing
    Set O = Nothing
    
    
    
    
    End Sub

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You test that field, or set a flag.
    I would probably set a flag blnSendMail to True at the start of the code after dimming it.

    Then if Null, set to False.

    Then only execute that code If blnSendMail is True
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 4
    Last Post: 09-08-2019, 11:45 AM
  2. How to send an Email based on Query Results
    By arich6211 in forum Access
    Replies: 2
    Last Post: 11-19-2018, 09:07 PM
  3. Create button to send email based on data in tbl
    By kiwikiki718 in forum Programming
    Replies: 12
    Last Post: 04-09-2017, 05:31 PM
  4. Send email to multiple recipients based on query
    By nablmm in forum Programming
    Replies: 3
    Last Post: 09-11-2014, 05:36 PM
  5. Send report based on Email selections
    By wnicole in forum Access
    Replies: 9
    Last Post: 10-02-2013, 07:51 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