Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 3rd, 2005, 01:38 PM
Authorized User
Join Date: Oct 2005
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Email Worksheet


I have a problem. I have a large workbook which has a number of worksheets, for which when I open the workbook the worksheets automatically gets hidden. Then the user form opens and allows the user to choose functions. I then want the user to be able to click send to IT and it opens one worksheet and sends that one worksheet alone without all the others and all the VBA code in it, is that possible. So when IT opens the emails and the spreadsheets, it is a somple one page document that they don't havr to enable macros

This is the code I have already

Private Sub cmd_enter_Click()

Dim confirm As Integer
Dim objOL As New Outlook.Application
Dim objMail As MailItem

Dim Details As Worksheet
Dim calendar As Worksheet
Dim Password As Worksheet
Dim timesheet As Worksheet
Dim main As Worksheet

Set Details = ActiveWorkbook.Worksheets("Details")
Set Password = ActiveWorkbook.Worksheets("Password")
Set calendar = ActiveWorkbook.Worksheets("calendar")
Set timesheet = ActiveWorkbook.Worksheets("timesheet")
Set main = ActiveWorkbook.Worksheets("main")

            Details.Visible = True
            Password.Visible = False
            calendar.Visible = False
            timesheet.Visible = False
            main.Visible = False

    Set objOL = New Outlook.Application
    Set objMail = objOL.CreateItem(olMailItem)

    confirm = MsgBox("Are you sure you want to do this?", vbQuestion + vbYesNo, "Alexander Mann Solutions New Starter Tool")

    If confirm = vbYes Then

    ActiveWorkbook.SendMail "EMAIL ADDRESS", "Update - New User", Null

    End If

If someone can help, it would be great
Old November 4th, 2005, 09:58 AM
Friend of Wrox
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts

A cool way of doing what you want is to use CDO (it gets around the annoying a virus may be trying to send an e-mail on your behalf warnings). See code below which should broadly do what you want although I suspect you'll want to refine a few of the rough edges. You'll definitely need to add a reference to the CDO object library in your project (in VBE: Tools -> References... and then check Microsoft CDO x.xx library) to get it to work but otherwise this should be good to go.


Public Sub SendEmail()

Dim i As Integer
Dim strSubject As String
Dim strMessage As String
Dim strRecipients As String
Dim sh As Worksheet
Dim wbSend As Workbook

Dim objSession As MAPI.Session
Dim objNewMessage As MAPI.Message
Dim objRecipient As MAPI.Recipient
Dim objAttachment As MAPI.Attachment

    ' Check the user is ok to send
    If MsgBox(Prompt:="Ok?", Buttons:=vbYesNo) = vbNo Then Exit Sub

    ' Set the subject of the e-mail
    strSubject = ""

    ' Set the text of the e-mail
    strMessage = ""

    ' Set the recipient names
    strRecipients = "me@anywhere.com;you@overthere.com"

    ' Save a tempory copy of the sheet with the correct filename
    Set sh = ThisWorkbook.Sheets("Details")
    Set wbSend = Workbooks.Add

    sh.Copy Before:=wbSend.Sheets(1)

    FName = "C:\..."
    wbSend.SaveAs Filename:=FName

    ' Close the Temp file
    wbSend.Close SaveChanges:=False

    ' Start CDO session
    Set objSession = New MAPI.Session
    objSession.Logon "", "", False, False

    ' Create a new message
    Set objNewMessage = objSession.Outbox.Messages.Add
    With objNewMessage

        .Subject = strSubject
        .Text = strMessage

        ' Add recipients one by one and resolve against the directory
        i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Do Until i = 0
            Set objRecipient = .Recipients.Add
            objRecipient.Name = Left(strRecipients, i - 1)
            strRecipients = Mid(strRecipients, i + 2)
            i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Set objRecipient = objNewMessage.Recipients.Add
        objRecipient.Name = strRecipients

    End With

    ' Attach the tempory file to the e-mail
    Set objAttachment = objNewMessage.Attachments.Add

    objAttachment.Position = 0

    objAttachment.Type = CdoFileData
    objAttachment.ReadFromFile FName
    objAttachment.Source = FName

    ' Delete the tempory copy
    Kill FName

    ' Send the e-mail

    'Release memory
    Set objNewMessage = Nothing
    Set objSession = Nothing
    Set objAttachments = Nothing
    Set objRecipient = Nothing

    ' Reassuring message
    MsgBox "File send successful"

End Sub
Old November 7th, 2005, 10:33 AM
Authorized User
Join Date: Oct 2005
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts

I am having trouble with that suggestion.

I get the following error message

Collaboration - Data Objects Mapi_e_not_found 8004010f)]]
Run Time Error 2147221233

Can someone help

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open up Worksheet alannoble26 Excel VBA 5 February 13th, 2006 02:24 PM
finding a worksheet ozPATT Excel VBA 4 November 16th, 2005 10:54 AM
Send Worksheet alannoble26 Excel VBA 3 November 2nd, 2005 01:04 PM
Setting Worksheet name marcusfromsweden XSLT 0 September 19th, 2005 11:50 AM
how to name worksheet by Month yylee Excel VBA 2 April 10th, 2004 01:01 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.