Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 12:46 AM.

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