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
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
 

shknbk2

Board Regular
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?
 

Some videos you may like

This Week's Hot Topics

Top