+ Reply to Thread
Results 1 to 4 of 4

Help: XL & Outlook

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-19-2006
    Posts
    142

    Question Help: XL & Outlook

    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

  2. #2
    Ivan Raiminius
    Guest

    Re: Help: XL & Outlook

    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


  3. #3
    Forum Contributor
    Join Date
    01-19-2006
    Posts
    142
    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?!

    Quote Originally Posted by 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

  4. #4
    Forum Contributor
    Join Date
    01-19-2006
    Posts
    142
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1