Forms are a great way to standardize information being gathered and used for any purpose. Excel is also a great tool for this as well. Unfortunately in todays easy to e-mail world people still would rather print out the form and fill it in by hand just to re-scan it into the computer or even worse fax it! Why is this? From what I have noticed and learned, it is because the extra steps required to save the form, open e-mail, compose the e-mail, select add an attachment, browse and find the attachment, and finally send of that internet message. Some of you may now be asking the question “How is printing it just to fill it out by hand and scan it in any easier?” The answer is simply the same technology that printed the form to begin with. Today’s copiers and multifunction devices have a very nice ability to scan directly to an attachment, or even better yet scan directly to an e-mail address. The problem is you end up with a hard to read eligible form, or a to light and distorted fax. What is the answer? Simple! Add a button to the bottom of the excel form to “Submit” (e-mail) the form using standard SMTP. Now the user fills out the form and only has one step to submitting….a button! This works great for about any form you want. Why did I come up with this? Well, where I work we fill out forms for all sorts of user equipment and new user requests. Now they can send the excel file straight to our support e-mail (which in turn goes directly to our help desk! I’ll give you some details about the form I used and the customizations that can be done.

The form was built in excel with a series of unlocked cell, check boxes, and drop downs. The submit button is ran by a VB Script (attached as a macro to a button). This VB Script pulls data from referenced cells that the use filled out. Data such as the “New User Name” to populate the name of the attachment, the “Requesters E-Mail” to populate the from e-mail address (we use this to populate the ticket requester automatically on our help desk). Naturally you can expand on these items to fill out the subject and destination of the e-mail. In the example below the destination is static however a simple variable referencing a cell could make this customized by input on the form. Please keep in mind this is a sample, no finalized, and definently does not has all of the error checking that should be placed in the VB Script. Now that you have a background on this I present to you my VB Script mashup.

*Please note this script is a mash up of two different examples found easily by searching the internet. Individually the scripts did not meet out needs, but combining parts of each did. Please give credit where it is due

Sub send_mail()

‘Working in 2000-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim FromField As String
Dim SubjectField As String
Dim TempFileFull As String
Dim DateString As String
Dim ManagerPath As String

DateString = “”
FromField = “”
SubjectField = “”
ManagerPath = “”

DateString = Range(“E4″).Text
FromField = Range(“L7″).Text
SubjectField = Range(“D6″).Text & ” – New Employee Request ”
MangerPath = “%userprofile%\”

If FromField = “” Then
FromField = InputBox(“Please Enter an E-mail Address”, “E – mail Is Missing”, “address@domain.tld”)
Range(“L7″).Value = FromField
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

‘Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

‘Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
‘You use Excel 2000-2003
FileExtStr = “.xls”: FileFormatNum = -4143
Else
‘You use Excel 2007-2010, we exit the sub when your answer is
‘NO in the security dialog that you only see when you copy
‘an sheet from a xlsm file with macro’s disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox “Your answer is NO in the security dialog”
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = “.xlsx”: FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = “.xlsm”: FileFormatNum = 52
Else
FileExtStr = “.xlsx”: FileFormatNum = 51
End If
Case 56: FileExtStr = “.xls”: FileFormatNum = 56
Case Else: FileExtStr = “.xlsb”: FileFormatNum = 50
End Select
End If
End If
End With

‘ ‘Change all cells in the worksheet to values if you want
‘ With Destwb.Sheets(1).UsedRange
‘ .Cells.Copy
‘ .Cells.PasteSpecial xlPasteValues
‘ .Cells(1).Select
‘ End With
‘ Application.CutCopyMode = False

‘Save the new workbook/Mail it/Delete it

If FromField = “” Then
FromField = “address@domain.tld”
Range(“L7″).Value = FromField
End If

TempFilePath = “c:” & “\”
TempFileName = Range(“D6″).Text & “-Form-205.xls”
TempFileFull = TempFilePath & TempFileName

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

With Destwb
.SaveAs TempFilePath & TempFileName
On Error Resume Next
On Error GoTo 0
.Close SaveChanges:=False
End With

Set objMessage = CreateObject(“CDO.Message”)
objMessage.Subject = SubjectField
objMessage.From = FromField
objMessage.To = “address@domain.tld”
objMessage.TextBody = “This is an e-mail from excel”
objMessage.AddAttachment TempFileFull

‘==This section provides the configuration information for the remote SMTP server.
‘==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2

‘Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.domain.tld”

‘Server port (typically 25)
objMessage.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

objMessage.Configuration.Fields.Update

‘==End remote SMTP server configuration section==

objMessage.Send

‘Delete the file you have send
Kill TempFilePath & TempFileName

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

MsgBox (“Your Form has Been Submitted. You will recieve a confirmation e-mail soon! Thank You — Company Name”)

End Sub