User Selected Variables

[Luke777]

Hi,

I have the below code (a lot of it is omitted for relevance).

Essentially, I have a worksheet. Column A has area names, B & C have associated email addresses.

I can write code that will send an email to the associated email easily enough, but I'm having great difficulty with selecting the Area.

I need to be able to populate a combo box based on A:A, once a selection is made, that selection is recorded in Area (string), but I also need the cell reference of where the selected area in A:A is so I can offset to get the recipients if that makes sense. Once the selection is made, the box goes away (hide as a I understand it rather than unload or selection data is lost?), the rest of the code resumes (which opens file dialogue for an attachment selection to be sent with an email).

This seems like something that should be simple but I'm unable to build a solution that works properly.

Sub Approval()

Dim Area As String
Dim Recipient1 As String
Dim Recipient2 As String
Depot = DepotCell(??)
Recipient1 = DepotCell.Offset(, 1)
Recipient2 = DepotCell.Offset(, 2)
Subject = Area & " Approval"

'code omitted
.To = Recipient1 & ";" &Recipient2

End Sub

For further reference, I intent to initiate the userform by running the macro from the quick access tool bar.

Thanks

 

[mumps]

Place this macro in a regular module:

Sub ShowUserform()
    UserForm1.Show
End Sub

Next create a button on the quick access tool bar and assign the above macro to it. Insert a userform into your workbook. In the userform insert a combobox and a commandbutton. These will be ComboBox1 and CommandButton1. Place the code below into the userform code module. Change the sheet name (in red) to suit your needs. When you click the button on the quick access tool bar, the userform will open. Make a selection in the combobox and click the commandbutton. In the pop up that appears, select your file and click "OK". The email will be created with the attachment.

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, FileChosen As Integer, OutApp As Object, OutMail As Object, fnd As Range
    Set fnd = Sheets("Sheet1").Range("A:A").Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select File"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = fnd.Offset(, 1) & ";" & fnd.Offset(, 2)
        .Subject = ""
        .HTMLBody = ""
        .attachments.Add FileName
        .Display
    End With
    Unload Me
    Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
End Sub

 

[Jeffrey Mahoney]

Please try this. Let's imagine that your combobox is on the same sheet with your area and email list and the named range for that is AreaSelection.

Sub Approval()

  Dim Area As String
  Dim Recipient1 As String
  Dim Recipient2 As String
  Dim DepotCell As Range
  Dim m As Long
  Dim Sht As Worksheet
  
  Set Sht = Sheets("Sheet1")

  m = Application.Match(Sht.Range("AreaSelection").Value, Sht.Range("A:A"), 0)
  
  Set DepotCell = Sht.Cells(m, 1)
  
  Recipient1 = DepotCell.Offset(, 1)
  
  Recipient2 = DepotCell.Offset(, 2)
  
  'Subject = Area & " Approval"
  
  
  
  'code omitted
  
  '.To = Recipient1 & ";" & Recipient2



End Sub