passing on variables to another sub

[Arie Bos]

Hello Excelleers!

I am struggling with the following: I have a user form with a two buttons.

1. Button A is linked to a code that opens/shows a worksheet for the user to fill in data;
2. With another button C on the worksheet, the worksheet code creates a folder and in that folder it saves a pdf from that worksheet. Both have the same name. After that, the worksheet is hidden and the user form pops up again. This works fine.
3. On the user form, button B is meant to create an email with the saved file as an attachment.

I do have a code that in itself works, but I cannot get the saved file as an attachment.
There are several variables used to create the folder and file, but I cannot get the to create the correct code to pull the file out and assign it to a variable, which can be used in the email definition.

I know this is a very open question. The codes are quite long, so I do not know if I can place them here. It seems I do not fully understand the rules of variables to be passed on from one sub to another.

 

[DanteAmor]

2. With another button C on the worksheet, the worksheet code creates a folder and in that folder it saves a pdf from that worksheet. Both have the same name. After that, the worksheet is hidden and the user form pops up again. This works fine.

This could be an option.

After saving the file, put the folder and file name in a cell on a sheet. If you have a parameter sheet, in that sheet allocate 1 cell to put the data.​

If you don't have a parameter sheet, then create a sheet and put the data in that sheet. The sheet can remain hidden.​

3. On the user form, button B is meant to create an email with the saved file as an attachment.

The button to send the email, check the cell of the parameters sheet, if the cell is empty you can send a warning message.​

If the cell has the data, then you can attach the file.​

I hope it helps you.

 

[Arie Bos]

Hi Dante, Yes, I have used that in the past, but I thought it cleaner to keep it in the VBA itself. Anyway, it is a good and effective solution and I use that now.
THanks,
Arie.

 

[DanteAmor]

I'll try to reproduce what you have, but it's the idea for you to take as a base and update your code.


In the module where you have your macros:

Public PathFile         'At the beginning of all code.

Sub Button_A()
  Sheets("Template").Visible = True
End Sub

Sub Button_C()
  Dim foldname As String
  Dim filename As String
  '
  'Here your code to create the folder
  foldname = "C:\trabajo\files\template\"
  filename = "template.pdf"
  PathFile = foldname & filename
  '
  Sheets("Template").ExportAsFixedFormat xlTypePDF, PathFile

  '
  Sheets("Template").Visible = False
End Sub

In the userform code:

Private Sub Button_B_Click()
  With CreateObject("Outlook.application").CreateItem(0)
    .To = "jov@email.com"
    .Subject = "example"
    .Body = "body..."
    .Attachments.Add PathFile
    .Display
  End With
End Sub