Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,570
- Office Version
- 365
- 2016
- Platform
- Windows
In my VBA project, a worksheet is created, copied to a new book and saved. This worksheet is like an internal user order form and has a few macro embedded shapes on it for use by the user using the form which is emailed to them.
The macros are stored in the workbook that created the initial worksheet. The users receiving the email with the form in it with the macro embedded shapes do not have access to the original creation workbook, so the macros embedded on the attachment they receive are not executable.
How can I send out that document with the macros available to the user? (I know this is can be an unsafe practice from the recipient standpoint, but no harm is intended with these macros.)
My email distribution code is below ...
"wb_tsrf" is the original worksheet created by my project that has the embedded macro shapes on it. It gets copied as a temporary file in which some of the shapes are removed (redundant to end user). It is this file that gets attached to the email. The remaining macros on the copied and emailed version don't work at the user end and I kinda hope they can be. I can use the macros on both the original and copied version as they can easily access the document holding the macros.
The macros are stored in the workbook that created the initial worksheet. The users receiving the email with the form in it with the macro embedded shapes do not have access to the original creation workbook, so the macros embedded on the attachment they receive are not executable.
How can I send out that document with the macros available to the user? (I know this is can be an unsafe practice from the recipient standpoint, but no harm is intended with these macros.)
My email distribution code is below ...
Code:
Sub Mail_workbook_Outlook_3()
'Working in Excel 2000-2016
'Mail a changed copy of the ActiveWorkbook with another file name
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Dim Shp As Shape
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Make a copy of the file/Open it/Edit it/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
FileExtStr = "." & LCase(Right(wb_tsrf.Name, Len(wb_tsrf.Name) - InStrRev(wb_tsrf.Name, ".", , 1)))
fnlen = Len(wb_tsrf.Name)
fnlen = fnlen - 5
fn = Left(wb_tsrf.Name, fnlen)
sn = Worksheets(1).Name
TempFileName = fn & "SR"
wb_tsrf.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
'**************Add code to edit the file here********************
With wb2.Worksheets(1)
.Unprotect
For Each Shp In ActiveSheet.Shapes
'MsgBox Shp.Name
If Shp.Name = "Group 12" Then
Shp.Delete
ElseIf Shp.Name = "Group 3" Then
Shp.Delete
End If
Next Shp
.Protect
End With
'Save the file after we changed it with the code above
wb2.Save
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "recipient@email.ca"
.CC = ""
.BCC = ""
.Subject = "### ##### ## SRF (" & sn & ")"
.Body = "The embedded macros are safe. Choosing to not enable them will not affect the document."
.Attachments.Add wb2.FullName
.Send 'or use .Display
End With
On Error GoTo 0
wb2.Close savechanges:=False
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Message has been sent."
Stop
End Sub"wb_tsrf" is the original worksheet created by my project that has the embedded macro shapes on it. It gets copied as a temporary file in which some of the shapes are removed (redundant to end user). It is this file that gets attached to the email. The remaining macros on the copied and emailed version don't work at the user end and I kinda hope they can be. I can use the macros on both the original and copied version as they can easily access the document holding the macros.
