Tosagua
asked on
Excel Macro - Outlook "Save & Send"
We have an Excel form on a Public Drive, that personnel can complete and email. The Shared worksheet and workbook are protected. Since this is a Public form used by all personnel, we do not want them to save the entries that they made. This way the next user will start with a blank form.
Previously, we instructed them to go to "File \ Save & Send \ Send As Attachment" to email the file. However, it is essential that the Subject Line is coded correctly, and that will never happen.
So we added a Button that creates an email, fills in the Subject Line, and attaches the Excel form. But when we receive the Excel form it is blank. Can "Save & Send \ Send As Attachment" be coded in a Macro ?
The code that we currently have is below.
Any insight or assistance would be greatly appreciated.
Tosagua
Previously, we instructed them to go to "File \ Save & Send \ Send As Attachment" to email the file. However, it is essential that the Subject Line is coded correctly, and that will never happen.
So we added a Button that creates an email, fills in the Subject Line, and attaches the Excel form. But when we receive the Excel form it is blank. Can "Save & Send \ Send As Attachment" be coded in a Macro ?
The code that we currently have is below.
Any insight or assistance would be greatly appreciated.
Tosagua
Private Sub CommandButton2_Click()
'Working in Excel 2000-2013
If Len(Dir(ThisWorkbook.Path & Application.PathSeparator & "Bypass Field Check.txt")) = 0 Then
For Each Cell In Sheets("PREMIUM FREIGHT APPROVAL FORM").[D7,D9,F42,G44,D46,D48,D50,D52,D54] ' <- change these cell references to suit
If Len(Cell) = 0 Then
MsgBox "There Are Entries Missing. All Information Is Required. "
Cell.Activate
Exit Sub
End If
Next Cell
End If
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = " "
.CC = ThisWorkbook.Sheets("PREMIUM FREIGHT APPROVAL FORM").Range("D7").Value
.Subject = ThisWorkbook.Sheets("PREMIUM FREIGHT APPROVAL FORM").Range("P64").Value
.Body = "Requesting Approval For Premium Freight Charges. Please Review And Advise."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
ASKER
Ejgil Hedegaard,
I apologize for the delay.
The code attached the form with the entries that were made. Perfect.
But we also need:
.To = " " - This in blank because the email could be sent to number of different people, not known in advance.
.CC = ThisWorkbook.Sheets("PREMI UM FREIGHT APPROVAL FORM").Range("D7").Value - This value on the form is the "Requester's Email Address, so on Reply, they will receive the approval (or not).
.Subject = ThisWorkbook.Sheets("PREMI UM FREIGHT APPROVAL FORM").Range("P64").Value - This value is a Reference Number on the form. Unique Identifier.
.Body = "Requesting Approval For Premium Freight Charges. Please Review And Advise."
Your assistance is appreciated.
Tosagua
Example:
Request-Email.docx
Code Used:
Private Sub CommandButton2_Click()
'Working in Excel 2000-2013
If Len(Dir(ThisWorkbook.Path & Application.PathSeparator & "Bypass Field Check.txt")) = 0 Then
For Each Cell In Sheets("PREMIUM FREIGHT APPROVAL FORM").[D7,D9,F42,G44,D46, D48,D50,D5 2,D54] ' <- change these cell references to suit
If Len(Cell) = 0 Then
MsgBox "There Are Entries Missing. All Information Is Required. "
Cell.Activate
Exit Sub
End If
Next Cell
End If
Application.Dialogs(xlDial ogSendMail ).Show "Recipients", "Subject"
With OutMail
.To = " "
.CC = ThisWorkbook.Sheets("PREMI UM FREIGHT APPROVAL FORM").Range("D7").Value
.Subject = ThisWorkbook.Sheets("PREMI UM FREIGHT APPROVAL FORM").Range("P64").Value
.Body = "Requesting Approval For Premium Freight Charges. Please Review And Advise."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0
End Sub
I apologize for the delay.
The code attached the form with the entries that were made. Perfect.
But we also need:
.To = " " - This in blank because the email could be sent to number of different people, not known in advance.
.CC = ThisWorkbook.Sheets("PREMI
.Subject = ThisWorkbook.Sheets("PREMI
.Body = "Requesting Approval For Premium Freight Charges. Please Review And Advise."
Your assistance is appreciated.
Tosagua
Example:
Request-Email.docx
Code Used:
Private Sub CommandButton2_Click()
'Working in Excel 2000-2013
If Len(Dir(ThisWorkbook.Path & Application.PathSeparator & "Bypass Field Check.txt")) = 0 Then
For Each Cell In Sheets("PREMIUM FREIGHT APPROVAL FORM").[D7,D9,F42,G44,D46,
If Len(Cell) = 0 Then
MsgBox "There Are Entries Missing. All Information Is Required. "
Cell.Activate
Exit Sub
End If
Next Cell
End If
Application.Dialogs(xlDial
With OutMail
.To = " "
.CC = ThisWorkbook.Sheets("PREMI
.Subject = ThisWorkbook.Sheets("PREMI
.Body = "Requesting Approval For Premium Freight Charges. Please Review And Advise."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0
End Sub
Application.Dialogs(xlDial ogSendMail ).Show, only accepts 3 arguments, Recipients, Subject, ReturnReceipt.
Recipients is the mail addresses to send to, divided with semicolon if more than one.
Subject is what you want as subject text, and can be
ThisWorkbook.Sheets("PREMI UM FREIGHT APPROVAL FORM").Range("P64").Value
In total
CC and Body is not possible to pre fill.
It is exactly the same as using Send as attachment, except To and Subject can be set.
When you use Send as attachment, you also has to fill To, CC, and Body.
Excel only set subject to the file name.
The rest you have to do.
If you want to use something like the OutMail code, with the possibility to add cc and body text, the mail addresses to send to must be known, because the final command .Display, sends the message without delay.
But attachments can only be saved files, so to send the file as it is now, it has to be saved with a different name than the empty form, and the saved file used as attachment.
But then you have a file you don't want, and the workbook in use will have the name used when saved.
The "temporary" file can not be deleted, since it is used now.
To avoid having a lot of not needed files, a workaround could be to delete all "temporary" files from yesterday and before, when the empty form is opened.
It requires that all files are saved in a known folder all users have access to save to.
The easiest is to use the folder where the empty form is, or a subfolder to that.
Recipients is the mail addresses to send to, divided with semicolon if more than one.
Subject is what you want as subject text, and can be
ThisWorkbook.Sheets("PREMI
In total
Application.Dialogs(xlDialogSendMail).Show " ", ThisWorkbook.Sheets("PREMIUM FREIGHT APPROVAL FORM").Range("P64").Value
opens the outlook dialog with "To" pre filled with a space (not blank, use "" for blank), and the value in P64 as subject, and the workbook attached as it is now.CC and Body is not possible to pre fill.
It is exactly the same as using Send as attachment, except To and Subject can be set.
When you use Send as attachment, you also has to fill To, CC, and Body.
Excel only set subject to the file name.
The rest you have to do.
If you want to use something like the OutMail code, with the possibility to add cc and body text, the mail addresses to send to must be known, because the final command .Display, sends the message without delay.
But attachments can only be saved files, so to send the file as it is now, it has to be saved with a different name than the empty form, and the saved file used as attachment.
But then you have a file you don't want, and the workbook in use will have the name used when saved.
The "temporary" file can not be deleted, since it is used now.
To avoid having a lot of not needed files, a workaround could be to delete all "temporary" files from yesterday and before, when the empty form is opened.
It requires that all files are saved in a known folder all users have access to save to.
The easiest is to use the folder where the empty form is, or a subfolder to that.
ASKER
Ejgil,
I went back to creating an Outlook Object, as that produces the email that we need.
But after the code, that checks the nine cells to ensure that they are filled in, I added "ThisWorkbook.Save". This is before the code that creates the email. Then after the code that creates the email and attchment I added "ThisWorkbook.Close". The attached form now retains all of the entries. Since the To: section on the email is empty, the .Display command does not send the email. It has to be filled in, and the Requester has to hit Send. This is blazing fast.
Now, a macro is needed that runs when the workbook is opened, to delete the values in the nine cells that required entries. This would, I hope, essentially create a blank form for the next user. Is this possible ?
Tosagua
I went back to creating an Outlook Object, as that produces the email that we need.
But after the code, that checks the nine cells to ensure that they are filled in, I added "ThisWorkbook.Save". This is before the code that creates the email. Then after the code that creates the email and attchment I added "ThisWorkbook.Close". The attached form now retains all of the entries. Since the To: section on the email is empty, the .Display command does not send the email. It has to be filled in, and the Requester has to hit Send. This is blazing fast.
Now, a macro is needed that runs when the workbook is opened, to delete the values in the nine cells that required entries. This would, I hope, essentially create a blank form for the next user. Is this possible ?
Tosagua
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ejgil,
So close. But these are Merged Cells, and you cannot change part of a Merged Cell.
However, if the Merged Cells are assigned names ("Cost", "Shipper", "Reciever", etc.) , can these ranges be listed to be cleared ?
Tosagua
So close. But these are Merged Cells, and you cannot change part of a Merged Cell.
However, if the Merged Cells are assigned names ("Cost", "Shipper", "Reciever", etc.) , can these ranges be listed to be cleared ?
Tosagua
ASKER
Ejgil,
I inserted this code into the ThisWorkbook module. But when the Email Receiver opens the attachment it is blank.
It seems that the code needs to be file specific.
End Sub
I inserted this code into the ThisWorkbook module. But when the Email Receiver opens the attachment it is blank.
It seems that the code needs to be file specific.
End Sub
Option Explicit
Private Sub Workbook_Open()
Range("D7:I7").ClearContents
Range("Freight_Cost").ClearContents
Range("F42:I42").ClearContents
Range("G44:I44").ClearContents
Range("D46:I46").ClearContents
Range("D48:I48").ClearContents
Range("D50:I50").ClearContents
Range("D52:I52").ClearContents
Range("D54:I57").ClearContents
End Sub
ASKER
Ejgil,
It was easier than I thought possible.
After the code that ensures all entries have been made: ThisWorkbook.Save
Then the code to create the email and attach the workbook
Then code to delete the cell contents.
ThisWorkbook.Save
ThisWorkbook.Close
The email has the completed form, and the next person to open the file, has a blank form.
I tried it three times and it worked well.
Now, we will put into a test group to ensure it is solid.
Greatly appreciate your assistance.
Tosagua
It was easier than I thought possible.
After the code that ensures all entries have been made: ThisWorkbook.Save
Then the code to create the email and attach the workbook
Then code to delete the cell contents.
ThisWorkbook.Save
ThisWorkbook.Close
The email has the completed form, and the next person to open the file, has a blank form.
I tried it three times and it worked well.
Now, we will put into a test group to ensure it is solid.
Greatly appreciate your assistance.
Tosagua
Private Sub CommandButton2_Click()
'Working in Excel 2000-2013
If Len(Dir(ThisWorkbook.Path & Application.PathSeparator & "Bypass Field Check.txt")) = 0 Then
For Each Cell In Sheets("PREMIUM FREIGHT APPROVAL FORM").[D7,D9,F42,G44,D46,D48,D50,D52,D54] ' <- change these cell references to suit
If Len(Cell) = 0 Then
MsgBox "There Are Entries Missing. All Information Is Required. "
Cell.Activate
Exit Sub
End If
Next Cell
End If
ThisWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = " "
.CC = ThisWorkbook.Sheets("PREMIUM FREIGHT APPROVAL FORM").Range("D7").Value
.Subject = ThisWorkbook.Sheets("PREMIUM FREIGHT APPROVAL FORM").Range("P64").Value
.Body = "Requesting Approval For Premium Freight Charges. Please Review And Advise."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Range("D7:I7").ClearContents
Range("Freight_Cost").ClearContents
Range("F42:I42").ClearContents
Range("G44:I44").ClearContents
Range("D46:I46").ClearContents
Range("D48:I48").ClearContents
Range("D50:I50").ClearContents
Range("D52:I52").ClearContents
Range("D54:I57").ClearContents
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
ASKER
Ejgil,
You got me pointed in the right direction.
I appreciate the help.
Tosagua
You got me pointed in the right direction.
I appreciate the help.
Tosagua
To avoid clear the cells, when the workbook is send, add an identifier in a cell somewhere, so the code to clear is skipped when the workbook opens.
Could be named ClearOnOpen on a hidden sheet.
Then before the workbook is saved before send, set ClearOnOpen to 0.
And after the workbook has been send, change ClearOnOpen to 1, and save the workbook again.
Or don't use the workbook open event to clear the cells.
Save the workbook before send.
Then clear the cells after send, and save the workbook again.
Then the identifier is not needed.
Code to clear in workbook event, also handling the merged cells.
Could be named ClearOnOpen on a hidden sheet.
Then before the workbook is saved before send, set ClearOnOpen to 0.
And after the workbook has been send, change ClearOnOpen to 1, and save the workbook again.
Or don't use the workbook open event to clear the cells.
Save the workbook before send.
Then clear the cells after send, and save the workbook again.
Then the identifier is not needed.
Code to clear in workbook event, also handling the merged cells.
Option Explicit
Private Sub Workbook_Open()
Dim Cell As Range
If [ClearOnOpen] = 1 Then
For Each Cell In Sheets("PREMIUM FREIGHT APPROVAL FORM").[D7,D9,F42,G44,D46,D48,D50,D52,D54]
If Cell.MergeArea.Address = Cell.Address Then
Cell.ClearContents
Else
Range(Cell.MergeArea.Address).ClearContents
End If
Next Cell
End If
End Sub
The posting crossed.
I see you have used the second option.
I see you have used the second option.
Use this instead, to opens the Excel send dialog, with recipients and subject.
No need to create an Outlook object.
Open in new window