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

RavosJ

New Member
Joined
Aug 27, 2019
Messages
8
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,140
Messages
5,484,974
Members
407,476
Latest member
PDT Inc

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top