L
LostwSQL
I have a form in Excel the tab named "Request Form" and another table
with the same field that I need to fill the form with in a different
workbook.
The table field showing below.
Subject Type of Request Severity Name E-Mail Channel 1 Channel 2 App1
App2 Date Initiative Describe
A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2
The request form has a bottom it would send an email when I finish
filling the form and it would generate random number to save it as
request number and file. I have over 3500 request to send that's why
I'm trying to find an automation way of doing it. Can you help create
the code to send the 3500 request in automation order?
Form field:
Subject = N7
Type of request = N10
Severity= N12
Name = J14
Email= R14
Channel1=N16
Channel2=R16
App1=J18
App2=N18
Date=J20
Initiative= R20
Describe=L22
Here is the code to submit the form
Private Sub Send_Form()
Dim strFrom As String
Dim TotalNum
AddIns("Analysis ToolPak").Installed = True
'Dark Blue = 11'
'White = 2'
If Range("R14") = "" Then
Range("R14").Select
Selection.Interior.ColorIndex = 6
MsgBox ("You must enter your E-Mail Address before Sending! Please
enter and Submit Again")
Exit Sub
End If
Range("R14").Select
Selection.Interior.ColorIndex = 2
Sheets("Values").Visible = True
Sheets("Values").Select
Range("H5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("H5").Select
Selection.NumberFormat = "0"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("I5").Select
Selection.NumberFormat = "0"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("J5").Select
Selection.NumberFormat = "0"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("L5").Select
Selection.NumberFormat = "0"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("M5").Select
Selection.NumberFormat = "0"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("N5").Select
Selection.NumberFormat = "0"
Range("O5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("O5").Select
Selection.NumberFormat = "0"
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Range("H6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("I5").Select
Selection.Copy
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J5").Select
Application.CutCopyMode = False
Selection.Copy
Range("J6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L5").Select
Application.CutCopyMode = False
Selection.Copy
Range("L6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Range("M5").Select
Selection.Copy
Range("M6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Range("N6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O5").Select
Application.CutCopyMode = False
Selection.Copy
Range("O6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
Sheets("Request Form").Select
TotalNum = Range("N98").Text
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.SaveAs _
Filename:="C:\" & "PSS Research Request # " & TotalNum & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.SendMail _
Recipients:="(e-mail address removed)", _
Subject:="PSS Research Request # " & TotalNum
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub
with the same field that I need to fill the form with in a different
workbook.
The table field showing below.
Subject Type of Request Severity Name E-Mail Channel 1 Channel 2 App1
App2 Date Initiative Describe
A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2
The request form has a bottom it would send an email when I finish
filling the form and it would generate random number to save it as
request number and file. I have over 3500 request to send that's why
I'm trying to find an automation way of doing it. Can you help create
the code to send the 3500 request in automation order?
Form field:
Subject = N7
Type of request = N10
Severity= N12
Name = J14
Email= R14
Channel1=N16
Channel2=R16
App1=J18
App2=N18
Date=J20
Initiative= R20
Describe=L22
Here is the code to submit the form
Private Sub Send_Form()
Dim strFrom As String
Dim TotalNum
AddIns("Analysis ToolPak").Installed = True
'Dark Blue = 11'
'White = 2'
If Range("R14") = "" Then
Range("R14").Select
Selection.Interior.ColorIndex = 6
MsgBox ("You must enter your E-Mail Address before Sending! Please
enter and Submit Again")
Exit Sub
End If
Range("R14").Select
Selection.Interior.ColorIndex = 2
Sheets("Values").Visible = True
Sheets("Values").Select
Range("H5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("H5").Select
Selection.NumberFormat = "0"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("I5").Select
Selection.NumberFormat = "0"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("J5").Select
Selection.NumberFormat = "0"
Range("L5").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("L5").Select
Selection.NumberFormat = "0"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("M5").Select
Selection.NumberFormat = "0"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("N5").Select
Selection.NumberFormat = "0"
Range("O5").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,9)"
Range("O5").Select
Selection.NumberFormat = "0"
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Range("H6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("I5").Select
Selection.Copy
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J5").Select
Application.CutCopyMode = False
Selection.Copy
Range("J6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L5").Select
Application.CutCopyMode = False
Selection.Copy
Range("L6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Range("M5").Select
Selection.Copy
Range("M6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N5").Select
Application.CutCopyMode = False
Selection.Copy
Range("N6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O5").Select
Application.CutCopyMode = False
Selection.Copy
Range("O6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
Sheets("Request Form").Select
TotalNum = Range("N98").Text
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.SaveAs _
Filename:="C:\" & "PSS Research Request # " & TotalNum & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.SendMail _
Recipients:="(e-mail address removed)", _
Subject:="PSS Research Request # " & TotalNum
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub