Results 1 to 2 of 2

Thread: Action Not in Line with Coding - Sent E-mail Goes to Previous E-mail Instead of Amended/Updated Code - Code Cleaner Needed? *FINAL STAGES*
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member RavosJ's Avatar
    Join Date
    Aug 2019
    Location
    Barcelona
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Action Not in Line with Coding - Sent E-mail Goes to Previous E-mail Instead of Amended/Updated Code - Code Cleaner Needed? *FINAL STAGES*

    I donīt really understand what is going wrong.

    I have changed the coding a bit recently and updated the e-mail address, however itkeeps sending the form to my old email that was in the original code....
    Is itbecause I have stored the code under the actual macro sheet and another oneunder the macro module?

    How I can fix this?

    --------------------------------------------I have stored this code under theactual sheet macro (so other actions, like hiding and unhiding of rows, happenwithin the sheet without having to press the send button all the time):



    Sub Mod_SendWorkbook()
    Dim OutlookMail As Object, msg As String
    '******validatefields******'
    If Range("D7").Value = "" Then msg = "Please SelectRequest Type on row 7"
    Select Case Range("E8").Value
    Case "": msg = "Please Select Requester Value on row 8"
    Case "Traveler": Rows("9:10").Hidden = True
    Case "Travel Arranger": Rows("9:10").Hidden = False
    End Select
    Select Case Range("E11").Value
    Case "": msg = "Please Select Value on row 8"
    Case "Employee": Rows("15:18").Hidden = True
    Case "Guest": Rows("15:18").Hidden = False
    End Select

    If Range("I12").Value = "" Then msg = "Please ProvideMobile Number on row 12"
    If Range("I13").Value = "" Then msg = "Please ProvideE-mail on row 12"
    If Range("I16").EntireRow.Hidden = False Then _
    If Range("I16").Value = "" Then msg = "Please ProvideGender on row 16"
    If Range("I15").EntireRow.Hidden = False Then _
    If Range("I15").Value = "" Then msg = "Please ProvideDOB on row 15"
    If Range("I13").Value = "" Then msg = "Please ProvideE-mail on row 12"
    If Range("I12").Value = "" Then msg = "Please ProvideMobile Number on row 12"
    If Range("D20").Value = "" Then msg = "Please SelectReason for Travel on row 20"
    If Range("D19").Value = "" Then msg = "Please SelectReason not Booked Online on row 19"
    If Range("D18").EntireRow.Hidden = False Then _
    If Range("D18").Value = "" Then msg = "Please SelectGuest Code on row 18"
    If Range("D17").EntireRow.Hidden = False Then _
    If Range("D17").Value = "" Then msg = "Please EnterThe Cost Center on row 17"
    If Range("D16").EntireRow.Hidden = False Then _
    If Range("D16").Value = "" Then msg = "Please CostCenter on row 15"
    If Range("D15").EntireRow.Hidden = False Then _
    If Range("D15").Value = "" Then msg = "Please SelectPayment Type on row 15"
    If Range("D14").Value = "" Then msg = "Please EnterTraveler Last Name on row 14"
    If Range("D12").Value = "" Then msg = "Please EnterTraveler First Name on row 12"
    If Range("E11").Value = "" Then msg = "Please SelectTraveler Value on row 11"
    If Range("I10").EntireRow.Hidden = False Then _
    If Range("I10").Value = "" Then msg = "Please EnterArranger Phone Number on row 10"
    If Range("I9").EntireRow.Hidden = False Then _
    If Range("I9").Value = "" Then msg = "Please EnterArranger e-mail on row 9"
    If Range("D10").EntireRow.Hidden = False Then _
    If Range("D10").Value = "" Then msg = "Please EnterArranger Last Name on row 10"
    If Range("D9").EntireRow.Hidden = False Then _
    If Range("D9").Value = "" Then msg = "Please EnterArranger First Name row 9"
    If Range("E8").Value = "" Then msg = "Please SelectRequester Value on row 8"
    If Range("D7").Value = "" Then msg = "Please SelectRequest Type on row 7"

    '******Send mail******'
    If msg <> "" Then
    MsgBox msg
    Exit Sub
    End If
    On Error Resume Next
    ThisWorkbook.Save
    Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)
    With OutlookMail
    .To = "xxxx@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("D6").Text
    .Body = "Please check attached file, thank you."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Send
    If Err.Number = 0 Then
    MsgBox "sent successfully"
    Else
    MsgBox "Sent error: " & Err.Number & " Description:" & Err.Description
    End If
    End With
    Set OutlookMail = Nothing
    End Sub

    --------------------------------------------Ihave stored this code under a module and attached it to a “button” to send theform (so the action happens when the send button gets pressed)::

    Sub Mod_SendWorkbook()
    Dim OutlookMail As Object, msg As String
    '******validatefields******'
    If Range("D7").Value = "" Then msg = "Please SelectRequest Type on row 7"
    Select Case Range("E8").Value
    Case "": msg = "Please Select Requester Value on row 8"
    Case "Traveler": Rows("9:10").Hidden = True
    Case "Travel Arranger": Rows("9:10").Hidden = False
    End Select
    Select Case Range("E11").Value
    Case "": msg = "Please Select Value on row 8"
    Case "Employee": Rows("15:18").Hidden = True
    Case "Guest": Rows("15:18").Hidden = False
    End Select

    If Range("I12").Value = "" Then msg = "Please ProvideMobile Number on row 12"
    If Range("I13").Value = "" Then msg = "Please ProvideE-mail on row 12"
    If Range("I16").EntireRow.Hidden = False Then _
    If Range("I16").Value = "" Then msg = "Please ProvideGender on row 16"
    If Range("I15").EntireRow.Hidden = False Then _
    If Range("I15").Value = "" Then msg = "Please ProvideDOB on row 15"
    If Range("I13").Value = "" Then msg = "Please ProvideE-mail on row 12"
    If Range("I12").Value = "" Then msg = "Please ProvideMobile Number on row 12"
    If Range("D20").Value = "" Then msg = "Please SelectReason for Travel on row 20"
    If Range("D19").Value = "" Then msg = "Please SelectReason not Booked Online on row 19"
    If Range("D18").EntireRow.Hidden = False Then _
    If Range("D18").Value = "" Then msg = "Please SelectGuest Code on row 18"
    If Range("D17").EntireRow.Hidden = False Then _
    If Range("D17").Value = "" Then msg = "Please EnterThe Cost Center on row 17"
    If Range("D16").EntireRow.Hidden = False Then _
    If Range("D16").Value = "" Then msg = "Please CostCenter on row 15"
    If Range("D15").EntireRow.Hidden = False Then _
    If Range("D15").Value = "" Then msg = "Please SelectPayment Type on row 15"
    If Range("D14").Value = "" Then msg = "Please EnterTraveler Last Name on row 14"
    If Range("D12").Value = "" Then msg = "Please EnterTraveler First Name on row 12"
    If Range("E11").Value = "" Then msg = "Please SelectTraveler Value on row 11"
    If Range("I10").EntireRow.Hidden = False Then _
    If Range("I10").Value = "" Then msg = "Please EnterArranger Phone Number on row 10"
    If Range("I9").EntireRow.Hidden = False Then _
    If Range("I9").Value = "" Then msg = "Please EnterArranger e-mail on row 9"
    If Range("D10").EntireRow.Hidden = False Then _
    If Range("D10").Value = "" Then msg = "Please EnterArranger Last Name on row 10"
    If Range("D9").EntireRow.Hidden = False Then _
    If Range("D9").Value = "" Then msg = "Please EnterArranger First Name row 9"
    If Range("E8").Value = "" Then msg = "Please SelectRequester Value on row 8"
    If Range("D7").Value = "" Then msg = "Please SelectRequest Type on row 7"

    '******Send mail******'
    If msg <> "" Then
    MsgBox msg
    Exit Sub
    End If
    On Error Resume Next
    ThisWorkbook.Save
    Set OutlookMail = CreateObject("Outlook.Application").CreateItem(0)
    With OutlookMail
    .To = "
    xxxx@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("D6").Text
    .Body = "Please check attached file, thank you."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Send
    If Err.Number = 0 Then
    MsgBox "sent successfully"
    Else
    MsgBox "Sent error: " & Err.Number & " Description:" & Err.Description
    End If
    End With
    Set OutlookMail = Nothing
    End Sub



    Any help and guidance is much appreciated.

    Kind Regards,

    Jonas

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    221
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Action Not in Line with Coding - Sent E-mail Goes to Previous E-mail Instead of Amended/Updated Code - Code Cleaner Needed? *FINAL STAGES*

    I'm not sure what the problem is, but maybe we can start to diagnose it. How about changing the ".Send" line in both of the macros with ".Display"? It won't automatically send, but it will open the email up so you can look at it first before sending. What is the email address that it shows?
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •