1

Situation: I am trying to create a form that will automatically attach itself to an email when clicking the Submit button, but once the submit button is clicked, the macros are removed from the document. Background: I have been able to create a code that will allow me to attach the document to an email; however, when the document attaches to the email, it still contains macros. Here is the code I have so far:

Public newfilename As String
Private Declare Function GetTempPath Lib "kernel32" _
  Alias "GetTempPathA" _
  (ByVal nBufferLength As Long, _
  ByVal lpBuffer As String) As Long
Private Declare Function GetTempFileName Lib "kernel32" _
  Alias "GetTempFileNameA" _
  (ByVal lpszPath As String, _
  ByVal lpPrefixString As String, _
  ByVal wUnique As Long, _
  ByVal lpTempFileName As String) As Long

Public Function Get_Temp_File_Name( _
  Optional sPrefix As String = "VBA", _
  Optional sExtensao As String = "") As String
  Dim sTmpPath As String * 512
  Dim sTmpName As String * 576
  Dim nRet As Long
  Dim F As String
  nRet = GetTempPath(512, sTmpPath)
  If (nRet > 0 And nRet < 512) Then
    nRet = GetTempFileName(sTmpPath, sPrefix, 0, sTmpName)
    If nRet <> 0 Then F = Left$(sTmpName, InStr(sTmpName, vbNullChar) - 1)
    If sExtensao > "" Then
      Kill F
      If Right(F, 4) = ".tmp" Then F = Left(F, Len(F) - 4)
      F = F & sExtensao
    End If
    Get_Temp_File_Name = F
  End If
End Function

Public Function Get_File_Name( _
  Optional sPrefix As String = "VBA", _
  Optional sFilename As String = "") As String
  Dim sTmpPath As String * 512
  Dim sTmpName As String * 576
  Dim nRet As Long
  Dim F As String
  nRet = GetTempPath(512, sTmpPath)
  a = Len(nRet)
  F = Left$(sTmpPath, InStr(sTmpPath, vbNullChar) - 1)
  Get_File_Name = F + sFilename
  Debug.Print F
End Function

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    ActiveDocument.SaveAs2 (newfilename)
    On Error Resume Next
    With OutMail
        .to = "me@me.com"
        .CC = ""
        .BCC = ""
        .Subject = "Communication with Government Regulatory Agency Report"
        .Body = "Attached is the Communication with Government Regulatory Agency report for the following location:"
        .Attachments.Add ActiveDocument.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Private Sub Document_Open()
newfilename = Get_File_Name("", "Communication with Government Regulatory Agency Report")
ActiveDocument.SaveAs2 (newfilename)
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 13 Then
        KeyCode = 0
        TextBox1.Text = TextBox1.Text & vbCrLf & "• "
    End If
End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 13 Then
        KeyCode = 0
        TextBox1.Text = TextBox1.Text & vbCrLf & "• "
    End If
End Sub

I have tried to code for making the command button invisible when clicked, and removing macros when command button is invisible and I have tried to code for changing the format of the document before it saves to the email, but neither solution worked. I am stuck, and as long as the document has active macros when attaching to the email, I cannot publish the form. Any assistance would be greatly appreciated!

novice
  • 11
  • 2
  • Why do you need to email active macro content? The typical solution is processing the document when it's returned - not on the recipient's machine. – Comintern Mar 28 '16 at 23:35
  • 1
    Do you want to send the `ActiveDocument` with the macros via the email? If you using a .dotm file, it only opens a new word document based on the template you created in the .dotm file.SO what you want, upon, clicking the CommandButton is for Word to save the form with the current data in and then to attach the saved form to an email? – Jean-Pierre Oosthuizen Mar 31 '16 at 08:21
  • I am not sure if VBA is what I need for this project. This document, with active macros, will be posted in our public library for managers to complete. I do not want the active maco to remain when the document is attached to the email. Sorry it took so long to respond. I am working on the project in down time. – novice Jun 27 '16 at 17:55
  • Save it as .docx in temp location then attached the saved docx to the email, then kill the docx file. – 0m3r Aug 25 '16 at 00:32

0 Answers0