The aim of my code is to read data from an excel sheet as well as a UserForm to create emails to be sent out with details. It is partly based on this tutorial: https://excelmacromastery.com/vba-userform/
I currently have three parts to my code:
- Userform code In my Module:
- SendEmails
- DisplayForm
DisplayForm creates the userform and initialises it/shows it. DisplayForm is called from the main sub SendEmails. I cannot find a way to use the form variables in SendEmails.
Sub SendEmails()
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim iRow As Integer
Dim FilterItem As String
Dim FilterColumn As Range
Dim FilterRange As Range
Call DisplayForm
Set OutApp = CreateObject("Outlook.Application")
Set ws = ThisWorkbook.Sheets("Test") 'Change to the name of your sheet
iRow = 2 'Start from the second row (assuming headers in row 1)
FilterItem = Trade
Set FilterColumn = ws.Columns("C") ' Change "C" to the column letter that contains the trade info
Set FilterRange = FilterColumn.Find(What:=FilterItem, LookIn:=xlValues)
MsgBox "Filter Item: " & FilterItem
MsgBox "Trade: " & Trade
If Not FilterRange Is Nothing Then
Do Until IsEmpty(ws.Cells(iRow, 1))
If ws.Cells(iRow, FilterColumn.Column).Value = FilterItem Then
''Rest of code to create email below
Sub DisplayForm()
' Create form
Dim frm As New UserForm1
'' Set frm = New UserForm1
' Display the UserForm
frm.Show
' Check if the user cancelled the UserForm
If frm.Cancelled = True Then
MsgBox "The UserForm was cancelled."
Else
MsgBox "You entered:" & vbCrLf & _
"Project name: " & frm.ProjectName & vbCrLf & _
"Doc transfer method: " & frm.DocTransfer & vbCrLf & _
"Start and finish date: " & frm.Start & " and " & frm.Finish & vbCrLf & _
"Price Type: " & frm.Prices & vbCrLf & _
"Trade: " & frm.Trade & vbCrLf & _
"Quote due date: " & frm.DueDate & vbCrLf & _
" "
End If
' Clean up
Unload frm
Set frm = Nothing
End Sub
I have tried:
- assigning values to variables in DisplayForm and then passing them to SendEmails.
- using values directly (as I have defined them in the public property within the form code)
Public Property Get Info() As String
PName = ProjectName.Value
Start = Start.Value
Finish = Finish.Value
Due = DueDate.Value
RFQtype = TypeRFQ.Value
DocTransf = DocTransfer.Value
Prices = Prices.Value
Trade = Trade.Value
End Property
- Something I was trying to look up but could not find an answer to is: if I cannot use the variables directly then do I need to call the Info function and in that case how would I go about that?
Thank you!
EDIT: I have set the "Dim frm As Userform1" line at the module level instead of in the DisplayForm sub. This seems to solve my issue but I was wondering was there any issue with doing that? does that then mean my "Public Property get Info" is obsolete? (I guess I am not fully sure what that function does here even after having googled extensively)