Help: XL & Outlook

[gti_jobert]

Hi all,

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


Code:
--------------------

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 = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.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 = "(e-mail address removed)"
.CC = "(e-mail address removed)"
.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?!

 

[gti_jobert]

Hi,

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


Code:
--------------------

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!