Okay, I have read through several examples and discussions about digitally
signing an excel workbook/code is not easy. My question deals with a mixture
of Excel and Outlook. I don't need to sign the Excel file, but I do need to
digitally sign the e-mail that is generated from within one of many Excel VBA
macros. I'm not sure if this action requires an additional "Add-in" or other
typically installed component to be "selected" or not.

An alternative would be to simply save the e-mail into the users Draft
folder or similar to allow the user to "forward" the message and apply the
appropriate Digital Signature. In order to meet the requirements posed I can
not first send the e-mail to the user and then let them forward and apply the
digital signature.

One problem I have with the code below in comparison to that required of me,
is that when I create NewBook (though it is only created in memory) it does
not take on the name that I want. The desire is that when the workbook is
sent to the user that the workbook filename meets the requirements I must
meet. I have determined that I could first save the workbook with the
desired name, but that would require me to delete it again or risk building
up a long list of unnecessary files.

The code that does the work that I have is as follows in a module called
'Email'. Elsewhere within the module the private subroutine EmailIndividual
is called, so that would be the "starting point" for the purpose of this
conversation:

Option Explicit

Dim NewBook As Workbook
Dim NewSheet As Worksheet

Private Sub CreateFile(IndName As String)
Dim FilePath As String
'Dim NewBook As Workbook

Application.StatusBar = "Opening the Workbook..."

Set NewBook = Workbooks.Add(xlWBATWorksheet)

With NewBook
.Title = IndName
.Subject = IndName
.Sheets(1).Name = IndName
With .Sheets(1).PageSetup
'Work is performed here to format the display of data, page
setup, Margins, Print Range, Print Range setup, headers, and footers.

End With
.Saved = True

End With

Set NewSheet = NewBook.Sheets(1)
NewBook.Activate

Application.StatusBar = False

End Sub

Private Sub EmailIndividual(Individual As IndReportCls)
Dim intI As Integer
Dim IndName As String
Dim Location As Long

Application.ScreenUpdating = False

'Make the file
Call CreateFile(Individual.GetColData(MthRepVars.GetNam e_Col))
'Create the copy page from those things to be emailed
CopySheet.Cells.Delete 'CopySheet is a worksheet that is hidden from the
user that receives data that can be copied for sending information particular
to the individual.
MonthReport.InsertHeader 'MonthReport is a module, and InsertHeader
inserts the desired header to the active worksheet.

Call Individual.SetEmailed(True) 'Individual is a class object that
supports a boolean variable to track whether the individual has been e-mailed
or not.

For intI = 1 To MonthReport.GetLastColumn
CopySheet.Cells(MonthReport.GetDataStart, intI).Value =
Individual.GetColData(intI) ' This portion of code copies the headers that
should appear in the e-mailed version of the data.
Next intI
Call Individual.SetEmailed(False)

MonthReport.InsertFooter
'Copy the page
CopySheet.Cells.Copy
NewSheet.Cells.PasteSpecial xlPasteAll

'Email them
NewBook.HasRoutingSlip = True

'Ensure the name has no unnecessary periods, so that the mail server can
handle it.
IndName = Individual.GetColData(MthRepVars.GetName_Col)
While InStr(IndName, ".") 0
Location = InStr(IndName, ".")
If Location = 1 Then
IndName = Right(IndName, Len(IndName) - Location)
ElseIf Location = Len(IndName) Then
IndName = Left(IndName, Location - 1)
Else
IndName = Left(IndName, Location - 1) + Right(IndName,
Len(IndName) - Location)
End If
Wend

On Error GoTo ErrorMailing
With NewBook.RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = IndName
'.Recipients = Array(IndName)
.ReturnWhenDone = False
.Subject = "[Subject Text Desired]"
.Message = "[Message that explains what this e-mail is about]"
End With
NewBook.Route
Call Individual.SetEmailed(True)

ErrorMailing:
NewBook.HasRoutingSlip = False
Err.Clear
On Error GoTo 0
NewBook.Saved = True
NewBook.Close

Application.ScreenUpdating = True
End Sub

"GB" wrote:

Okay, I have read through several examples and discussions about digitally
signing an excel workbook/code is not easy. My question deals with a mixture
of Excel and Outlook. I don't need to sign the Excel file, but I do need to
digitally sign the e-mail that is generated from within one of many Excel VBA
macros. I'm not sure if this action requires an additional "Add-in" or other
typically installed component to be "selected" or not.

An alternative would be to simply save the e-mail into the users Draft
folder or similar to allow the user to "forward" the message and apply the
appropriate Digital Signature. In order to meet the requirements posed I can
not first send the e-mail to the user and then let them forward and apply the
digital signature.

Anyone?!

"GB" wrote:

One problem I have with the code below in comparison to that required of me,
is that when I create NewBook (though it is only created in memory) it does
not take on the name that I want. The desire is that when the workbook is
sent to the user that the workbook filename meets the requirements I must
meet. I have determined that I could first save the workbook with the
desired name, but that would require me to delete it again or risk building
up a long list of unnecessary files.

The code that does the work that I have is as follows in a module called
'Email'. Elsewhere within the module the private subroutine EmailIndividual
is called, so that would be the "starting point" for the purpose of this
conversation:

Option Explicit

Dim NewBook As Workbook
Dim NewSheet As Worksheet

Private Sub CreateFile(IndName As String)
Dim FilePath As String
'Dim NewBook As Workbook

Application.StatusBar = "Opening the Workbook..."

Set NewBook = Workbooks.Add(xlWBATWorksheet)

With NewBook
.Title = IndName
.Subject = IndName
.Sheets(1).Name = IndName
With .Sheets(1).PageSetup
'Work is performed here to format the display of data, page
setup, Margins, Print Range, Print Range setup, headers, and footers.

End With
.Saved = True

End With

Set NewSheet = NewBook.Sheets(1)
NewBook.Activate

Application.StatusBar = False

End Sub

Private Sub EmailIndividual(Individual As IndReportCls)
Dim intI As Integer
Dim IndName As String
Dim Location As Long

Application.ScreenUpdating = False

'Make the file
Call CreateFile(Individual.GetColData(MthRepVars.GetNam e_Col))
'Create the copy page from those things to be emailed
CopySheet.Cells.Delete 'CopySheet is a worksheet that is hidden from the
user that receives data that can be copied for sending information particular
to the individual.
MonthReport.InsertHeader 'MonthReport is a module, and InsertHeader
inserts the desired header to the active worksheet.

Call Individual.SetEmailed(True) 'Individual is a class object that
supports a boolean variable to track whether the individual has been e-mailed
or not.

For intI = 1 To MonthReport.GetLastColumn
CopySheet.Cells(MonthReport.GetDataStart, intI).Value =
Individual.GetColData(intI) ' This portion of code copies the headers that
should appear in the e-mailed version of the data.
Next intI
Call Individual.SetEmailed(False)

MonthReport.InsertFooter
'Copy the page
CopySheet.Cells.Copy
NewSheet.Cells.PasteSpecial xlPasteAll

'Email them
NewBook.HasRoutingSlip = True

'Ensure the name has no unnecessary periods, so that the mail server can
handle it.
IndName = Individual.GetColData(MthRepVars.GetName_Col)
While InStr(IndName, ".") 0
Location = InStr(IndName, ".")
If Location = 1 Then
IndName = Right(IndName, Len(IndName) - Location)
ElseIf Location = Len(IndName) Then
IndName = Left(IndName, Location - 1)
Else
IndName = Left(IndName, Location - 1) + Right(IndName,
Len(IndName) - Location)
End If
Wend

On Error GoTo ErrorMailing
With NewBook.RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = IndName
'.Recipients = Array(IndName)
.ReturnWhenDone = False
.Subject = "[Subject Text Desired]"
.Message = "[Message that explains what this e-mail is about]"
End With
NewBook.Route
Call Individual.SetEmailed(True)

ErrorMailing:
NewBook.HasRoutingSlip = False
Err.Clear
On Error GoTo 0
NewBook.Saved = True
NewBook.Close

Application.ScreenUpdating = True
End Sub

"GB" wrote:

Okay, I have read through several examples and discussions about digitally
signing an excel workbook/code is not easy. My question deals with a mixture
of Excel and Outlook. I don't need to sign the Excel file, but I do need to
digitally sign the e-mail that is generated from within one of many Excel VBA
macros. I'm not sure if this action requires an additional "Add-in" or other
typically installed component to be "selected" or not.

An alternative would be to simply save the e-mail into the users Draft
folder or similar to allow the user to "forward" the message and apply the
appropriate Digital Signature. In order to meet the requirements posed I can
not first send the e-mail to the user and then let them forward and apply the
digital signature.