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

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top