Email Worksheet

alannoble26 · November 3rd, 2005, 01:38 PM

Hello,

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

maccas · November 4th, 2005, 09:58 AM

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.

Cheers,
Maccas

Code:

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 = "[email protected];[email protected]"

    ' 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)
            objRecipient.Resolve
            strRecipients = Mid(strRecipients, i + 2)
            i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Loop
        Set objRecipient = objNewMessage.Recipients.Add
        objRecipient.Name = strRecipients
        objRecipient.Resolve

    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
    objNewMessage.Update
    objNewMessage.Send

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

    ' Reassuring message
    MsgBox "File send successful"

End Sub

alannoble26 · November 7th, 2005, 10:33 AM

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