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.GetName_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 said:
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.
