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
KP_SoCalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.