Excel says that it is not connected and shows an error on the line where "myInbox" is set to the Outlook inbox.
My studies led me to these references and Outlook commands. Then online there was a snippet of code using "for next" to find all the new emails and they had to increment but they couldn't increment an object so they used i
in the nested "for next" but then they tried to increment the folder in the outer "for next". Unk
did not alleviate either when the "for next" was modified. When "olInbox" is used in place where "myInbox" is set to olFolderInbox still same error so not expecting a MAPI.
I am trying to get a variable equal to the subject of the Outlook email. Pretty sure we will not need the nested "for next" but they are included (commented out) so no work is lost.
The error returned is "you are not connected" and "run time"
I expect:
Inbox of unread e mails becomes read.
All the attachments are saved in a folder.
The important emails identified through subject line should get a reply.
This How to retrieve emails from Outlook using Excel VBA? uses an object to define their "myInbox" variable and then sets it equal to the Outlook folder. I tried to mimic it but same error and not seeing the difference
Dim olInbox As Outlook.MAPIFolder
Dim myInbox As Outlook.Folder 'does not change error if we switch this to object
Dim unRead, m As Object
Dim att As Object
Dim emailSubject As String
Dim newEmailItem As Outlook.MailItem
Dim x As Date
Dim ws As Worksheet
Dim i As Long
Dim row As Long
Dim unk As Integer
x = Date
'~~> Get Outlook instance
Set EmailApp = New Outlook.Application
Set myNameSpace = Outlook.GetNamespace("MAPI")
'For unk = 1 To 2 Step 1
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox).Folder 'HERE IS WHERE MY ERROR IS
'using "olInbox" in place of "myInbox" does not solve it either so its not expecting MAPI
'Set olInbox = myNameSpace.GetDefaultFolder(olFolderInbox).Folders(myInbox.Name)
'For i = olInbox.Items.Count To 1 Step -1
'If TypeOf olInbox.Items(i) Is MailItem Then
Set newEmailItem = olInbox.Items(i)
' If newEmailItem(newEmailItem.Subject, "transactions") > 0 _
' And newEmailItem(newEmailItem.ReceivedTime, x) > 0 Then
' With ws
' row = .Range("A" & .Rows.Count).End(xlUp).row
' .Range("A" & row).Offset(1, 0).Value = newEmailItem.Subject
' .Range("A" & row).Offset(1, 1).Value = newEmailItem.ReceivedTime
' .Range("A" & row).Offset(1, 2).Value = newEmailItem.SenderName
' End With
' End If
'End If
'Next i
'Set olInbox = Nothing
'Next unk or myInbox?
'set unread equal to the count of unread e mails in the inbox
Set unRead = myInbox.Items.Restrict("[UnRead] = True")
File_Path = "D:\Documents\Email attachments\" 'where we save the attachments
If unRead.Count = 0 Then
MsgBox "NO Unread Email In Inbox"
Else
For Each m In unRead
emailSubject = newEmailItem.Subject
Select Case emailSubject
Case emailSubject Like "MOI"
Set newEmailItem = EmailApp.CreateItem(olMailItem) 'creates a new e mail to be sent
newEmailItem.To = "chase.bcbengineering@gmail.com" 'who your sending it to, we will need to make dynamic
newEmailItem.Subject = "MOI" 'enters MOI into new e mail subject line"
'below is the body of the e mail
newEmailItem.HTMLBody = "Hi," & vbNewLine & "Branagan Ins here just wanted to let you know there is a new MOI" & vbNewLine & "have a great week" & vbNewLine & "Branagan Ins Services" & vbNewLine & "707-255-2500" & vbNewLine & "Marilyn Branagan" & vbNewLine & "1631 Lincoln ave, Napa CA"
If m.Attachments.Count > 0 Then
For Each att In m.Attachments
att.SaveAsFile File_Path & "att.Filename" 'might need to make dynamic
m.unRead = False 'mark email as read
DoEvents
m.Save
EmailApp.Attachments.Add File_Path & "att.Filename" 'attach att to new e mail out
Next att
End If
newEmailItem.Send
Case emailSubject Like "Renewal"
Set newEmailItem = EmailApp.CreateItem(olMailItem) 'creates a new e mail to be sent
newEmailItem.To = "chase.bcbengineering@gmail.com" 'who your sending it to, we will need to make dynamic
newEmailItem.Subject = "Renewal" 'enters MOI into new e mail subject line"
'below is the body of the e mail
newEmailItem.HTMLBody = "Hi," & vbNewLine & "Branagan Ins here just wanted to let you know your policy is renewing" & vbNewLine & "have a great week" & vbNewLine & "Branagan Ins Services" & vbNewLine & "707-255-2500" & vbNewLine & "Marilyn Branagan" & vbNewLine & "1631 Lincoln ave, Napa CA"
If m.Attachments.Count > 0 Then
For Each att In m.Attachments
MsgBox "you saved your attachements"
att.SaveAsFile File_Path & "att.Filename" 'might need to make dynamic
m.unRead = False
DoEvents
m.Save
EmailApp.Attachments.Add File_Path & "att.Filename" 'attach att to new e mail out
Next att
End If
newEmailItem.Send
Case Else
m.unRead = False 'marks all messages as read
End Select
Next m
End If
End Sub
Add a call to Namespace.Logon
and get rid of the .Folder
part.
Set EmailApp = New Outlook.Application
Set myNameSpace = Outlook.GetNamespace("MAPI")
myNameSpace.Logon
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)