Help: XL & Outlook
 

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 = "
.CC = "
.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


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545045

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 = "
.CC = "
.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

Help: XL & Outlook
 

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?!

Ivan Raiminius Wrote:
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 = "
.CC = "
.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
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545045

Help: XL & Outlook
 

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!


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545045