Help: XL & Outlook

[gti_jobert]

Hi all,

I've created a Sub to send an email from excel as shown;

Sub SendAnEmailWithOutlook(CurrFile)
    
    Dim olApp As Outlook.Application
    Dim olMail As MailItem

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With olMail
        .To = "RLodge@tfs.textron.com"
        .CC = "JPalin@tfs.textron.com"
        .Subject = "Textron Schedule Agreements: " & Right(CurrFile, 13)
        .Attachments.Add CurrFile & ".xls"
        .Display
        .OriginatorDeliveryReportRequested = True
        .ReadReceiptRequested = True
    End With
     
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

I need to find out how I can determine whether or not the email had actually been sent or not.

Any help appreciated! TIA

[Ivan Raiminius]

Hi,

Sub SendAnEmailWithOutlook(CurrFile)

Dim olApp As Outlook.Application
Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = "RLo...@tfs.textron.com"
.CC = "JPa...@tfs.textron.com"
.Subject = "Textron Schedule Agreements: " & Right(CurrFile, 13)
.Attachments.Add CurrFile & ".xls"
.Display
.OriginatorDeliveryReportRequested = True
.ReadReceiptRequested = True
..send 'added to send the mail
if .sent then
msgbox "Sent"
else
msgbox "Not sent"
end if
End With

Set olMail = Nothing
Set olApp = Nothing
End Sub

But I cannot see in the code you are sending it. (you need to add
".send").

Regards,
Ivan

[gti_jobert]

If I add .Send to the With statement then the email message will try and send it automatically - I want the user to be able to add text the the body of the email first before sending it.

Not sure what I can do from here?!

Hi,

Sub SendAnEmailWithOutlook(CurrFile)

Dim olApp As Outlook.Application
Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = "RLo...@tfs.textron.com"
.CC = "JPa...@tfs.textron.com"
.Subject = "Textron Schedule Agreements: " & Right(CurrFile, 13)
.Attachments.Add CurrFile & ".xls"
.Display
.OriginatorDeliveryReportRequested = True
.ReadReceiptRequested = True
..send 'added to send the mail
if .sent then
msgbox "Sent"
else
msgbox "Not sent"
end if
End With

Set olMail = Nothing
Set olApp = Nothing
End Sub

But I cannot see in the code you are sending it. (you need to add
".send").

Regards,
Ivan

[gti_jobert]

Hi,

I have a Function where it checks in the Sent Items Folder;

Sub VerifyEmail(CurrFile, sentMail)
     
    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem
    Dim lngRow As Long
    Dim intAtt As Integer
    Dim wbkTemp As Workbook
    Dim strTempFile As String
     
    Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.GetDefaultFolder(olFolderSentMail)
     
    ' go thru all mail in Sent Items
    For Each olMail In olFolder.Items
         ' only check those with attachments
        For intAtt = 1 To olMail.Attachments.count
             ' only those with xls files
            If InStr(1, olMail.Attachments(intAtt).FileName, ".xls", vbTextCompare) > 0 Then
                 ' get folder and filename for xls file
                strTempFile = ThisWorkbook.Path & Application.PathSeparator & olMail.Attachments(intAtt).FileName
                 ' save it so we can open and read it
                olMail.Attachments(intAtt).SaveAsFile strTempFile
                Set wbkTemp = Workbooks.Open(strTempFile)
                 If Right(CurrFile, 13) & ".xls" = olMail.Attachments(intAtt).FileName Then
                    sentMail = True
                 End If
                 ' close and destroy temporary excel file
                wbkTemp.Close False
                Set wbkTemp = Nothing
                Kill strTempFile
            End If
        Next
    Next
    Set olMail = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
End Sub

This basically finds all the the attachment file names (in Sent Items Folder), if an attachment name is the same as the currFile then the file has been sent.......BUT I dont know how I can implement it into my code so it runs when Oulook has closed?!

again, all ideas appreciated on this one! Cheers!