Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Email Macro Assistance

0

Hi and Help,

I'm looking to create a macro that has 3 possible outcomes actions that will be in Column I - Updated, Fixed or More Information.

Update will send to a set email (see the information below - i could only code 1 outcomes)

Fixed and More Informatin will send to the same email address.

How do I get it all in code?  As stated, my code below is what will go for Update - help, I'm not sure how to create the condition for the other two and then add its email info.

Any help would be appreciated.

Shonda

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("k:k"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = "Update Request" Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "All," & vbNewLine & vbNewLine & _
    "Updates have been entered into the maintenance log:" & vbNewLine & _
Range("b3") & vbNewLine & _
Range("c3") & vbNewLine & _
Range("d3")
      With xOutMail
        .To = "xxxxxx@xxxxx.com"
        .CC = ""
        .BCC = ""
        .Subject = "Maintenance Updates"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
excelmacros-vbaformulas-functions
MS Office Version: 2016
posted by: shondab on Wed Jul 29, 2020 at 10:24 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

I put your code inside CODE tags for you, but also I removed your email, be careful posting code with that in there ;)
don (rep: 1989) Jul 30, '20 at 2:35 am
Add to Discussion

Answers

0

Hi Shonda,

There are two ways that you could do this, have 3 email macros, one for each condition, or put a condition check within the email macro and send it a variable.

I think that the easiest thing for you to manage would be 3 email macros - it is more code but easy to change/update.

Change this:

If Target.Value = "Update Request" Then
    Call Mail_small_Text_Outlook
End If

to something like this:

If Target.Value = "Update Request" Then

    Call Mail_small_Text_Outlook

ElseIf Target.Value = "Updated" Then
    ' Do something if it equals "Updated"

    Call another_macro

ElseIf Target.Value = "Fixed" Then
    ' Do something if it equals "Fixed"

    Call another_another_macro

Else
    ' Do something if it doesn't equal any of the above options.
    ' You don't have to include this section if you don't need it.

End If

Then change another_macro and another_another_macro to the new email macros that you will make by copy/pasting the working email macro.

If you would rather move the IF statement check to the email macro and send it the value of the target cell, that can also be done.

posted by: don (rep: 1989) on Thu Jul 30, 2020 at 2:42 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login