• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Send Active Excel Workbook from Excel via Outlook

I need to tailor it just a little bit.  When running the sub, I need it to do the following:

1)      Save Active Workbook.
2)      Fire some miscellaneous code (e.g. Call MiscCode)
3)      Attach the active workbook in an Outlook email with the date time stamp included in the file name attachement.
4)      Close ActiveWorkbook WITHOUT saving changes.
5)      Screen focus should be on the email draft with the attached file.

Both of the codes below get me close to what I need.  It just needs an expert’s touch to get it where it can meet my needs as described above.  I attached an Excel file if you’re curious to see how the codes below work.

SendMail-Attachment-Testers.xls
0
KP_SoCal
Asked:
KP_SoCal
  • 2
1 Solution
 
Ken ButtersCommented:
Attached is the code to accomplish what you asked for.

In VB Editor make sure you select tools/references and check mark "Microsoft Outlook 12.0 Object Library"

Attached is the code to accomplish what you asked for.

In VB Editor make sure you select tools/references and check mark "Microsoft Outlook 12.0 Object Library"

Open in new window

0
 
Ken ButtersCommented:
Sorry... cut and paste issues.

Try this.
Sub SaveAsDraft()
    Dim objOutlook As Object
    Dim objMailMessage As Outlook.MailItem
    Dim emlBody, sendTo As String
    Dim wkbook As String
    
    ' Step 1.  Save Active Workbook
    
    ActiveWorkbook.Save
    
    ' Step 2.  Call MiscCode
    
    Call MiscCode
    
    ' Step 3. Create Email
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMailMessage = objOutlook.CreateItem(0)
    sendTo = "weasel@mymail.com"
    emlBody = "My special message"
    
    
    With objMailMessage
        .To = sendTo
        .Body = emlBody
        .Subject = "Save as Draft Test"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With

   ' Step 4. Close ActiveWorkbook without saving
   
   ActiveWorkbook.Close
    
End Sub

Sub MiscCode()
' todo do something here

End Sub

Open in new window

0
 
KP_SoCalAuthor Commented:
Hi there, sorry to respond so late.  I was hoping to avoid using a code that requires me to reference the object library.  But there are some valuable things I picked up from your code suggestion.  I'm going to dig into this more tomorrow morning.  Don't worry, I won't forget about your points. =)
0

Featured Post

Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now