VBA, Excel and MAPI

Hey,

Let me preface this message with "I AM NOT A PROGRAMMER!"...<sigh>

I've created a couple of VBA macros that will either send the active workbook via CDO (hitting a SMTP server) or Exchange (using Outlook objects).  I need advice:

CDO sends fine, but it leaves no "sent message" in my Sent Items - or at least it doesn't through my code.

The other way, DOES give me my sent message but it doesn't allow me to change the sender of the message.

I'm creating a tab in the workbook called Recipients - and in the first column I put email addresses, of contacts in Exchange/AD.

Ideally, I would like to have code that will save the workbook, create a session to a mailbox that I have permissions to, send the email and place the sent item in the special mailbox's Sent Items, leaving evidence that the sheet left our site.  I'll include the code I'm using that uses Outlook objects:

***************CODE*********************
Option Explicit

Sub SendWorkbook()
    Dim OutlookApp As Outlook.Application
    Dim MItem As Outlook.MailItem
    Dim Filename As String
    Dim EmailAddr As String
    Dim cell As Range
    Dim Subj As String
    Dim Recipient As String
    Dim Msg As String
    Dim Attach As Outlook.Attachment
    Dim Book As Workbook
   
    Filename = ThisWorkbook.Name
   
    ActiveWorkbook.Save
       
    Set OutlookApp = New Outlook.Application
For Each cell In Worksheets("Recipients").Range("A1:A100").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
        Subj = Filename
   EmailAddr = cell.Value
   
    Msg = "See Attached File" & vbCrLf
    Msg = Msg & vbCrLf & vbCrLf
    Msg = Msg & "PA" & vbCrLf
    Msg = Msg & "615.555.1212"
   
       
    Set MItem = OutlookApp.CreateItem(olMailItem)
    With MItem
        .To = EmailAddr
        .Subject = "PA Document - " & ActiveWorkbook.Name
        .Body = Msg
        .Attachments.Add ActiveWorkbook.FullName
        .Send
   
       
    End With
    End If
    Next
     
End Sub
**************END CODE******************
The above code works, but I have to be signed into the special mailbox's profile - which means little to me, but I'm dealing with people that think they're too important to learn HOW, but are just either too lazy or too...ummmm...stupid (did I say that out loud?).

Below is the code (I got off this site actually) using CDO, that I've been dinking with:

***************CODE*********************

Sub CDO_Send_Workbook()
' This example use late binding, you don't have to set a reference
' You must be online when you run the sub
    Dim iMsg As Object
    Dim iConf As Object
    Dim wb As Workbook
    Dim WBname As String
    Dim Flds As Variant
   
    ActiveWorkbook.Save
 
    Application.ScreenUpdating = False
    Set wb = ActiveWorkbook
'    Or use this Set wb = ThisWorkbook
'    WBname = wb.Name & " " & Format(Now, "dd-mm-yy") & ".xls"
'    wb.SaveCopyAs "C:/" & WBname
'    It will save a copy of the file in C:/ with a Date and Time stamp
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.PA.local"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
 
    'Check out the Tips section if you want to change the .To and .TextBody
    With iMsg
        Set .Configuration = iConf
        .To = "anyone@IChoose.com"
        .CC = ""
        .BCC = ""
        .From = """PA Pricing"" <PAPricing@PATN.com>"
        .Subject = "This is a test"
        .TextBody = "This is the body text"
        .AddAttachment "C:/" & WBname
        ' You can add any file you want with this line .AddAttachment "C:/Test.txt"
        .Send
    End With
 
'    Kill "C:/" & WBname    'If you not want to delete the file you send delete this line
'    Set iMsg = Nothing
'    Set iConf = Nothing
'    Set wb = Nothing
'    Application.ScreenUpdating = True
End Sub

**************END CODE******************

Not being a Programmer, you can imagine how fried my brain is after two days of this.  Help is SO APPRECIATED!

paadmin (aka: Hank)