I have a macro to send emails using excel, but i would like it to set a 10 day flag in outlook automatically as well on the SENT email.

I have searched but have been unable to find a solid solution to create the follow up flag in outlook using excel.  I need to set the reminder for myself to follow back up on the email with the recipient.  Is this even possible?
ret2855Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
you can try to customize this codes, this codes will retrieved the last email sent and then set the flag with reminder accordingly.
Private Const olFolderSentMail As Long = 5

Sub setSendMailFlag()
    Dim oApp As Object
    Dim oFolder As Object
    Dim oItem As Object
    Dim bCloseAfterDone As Boolean
    Dim itms As Object
    
    On Error Resume Next
    Set oApp = GetObject(, "Outlook.Application")
    If oApp Is Nothing Then
        bCloseAfterDone = True
        Set oApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    
    Set oFolder = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail)
    Set itms = oFolder.Items
    itms.Sort "[ReceivedTime]", True
    
    For Each Item In itms
        If TypeName(Item) = "MailItem" Then
            With Item
                .MarkAsTask olMarkNextWeek
                .TaskStartDate = Date + 10
                .TaskDueDate = Date + 10
                .FlagRequest = "Reminder"
                .ReminderSet = True
                .ReminderTime = Date + 10
                .Save
            End With
            
            'Change your logic accordingly
            Exit For
            
        End If
    Next
    
    If bCloseAfterDone Then
        oApp.Quit
    End If
    Set oApp = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ret2855Author Commented:
Ryan, just thought about this but will this code work if i am manually sending the emails after excel generates them (i.e. using ".display" instead of "send".  My code is below, any suggestions on how to incorporate your code into mine if possible?
Sub Procedure1()

  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OApp As Object, OMail As Object, signature As String
  
  ' Not sure for what the Title is
  Title = Range("A1")

  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & Sheets("Christmas").Name & ".pdf"
 
  ' Export activesheet as PDF
  With Sheets("Christmas")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard<wbr ></wbr><wbr ></wbr>, IncludeDocProperties:=True<wbr ></wbr><wbr ></wbr>, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OApp = CreateObject("Outlook.Appl<wbr ></wbr><wbr ></wbr>ication")
    Set OMail = OApp.CreateItem(0)
    With OMail
    .Display
    End With
        signature = OMail.HTMLbody
  If Err Then
    Set OApp = CreateObject("Outlook.Appl<wbr ></wbr><wbr ></wbr>ication")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
  
  ' Prepare e-mail with PDF attachment
  With OMail
   
    ' Prepare e-mail
    .Subject = "Christmas: " & Sheets("Information").Rang<wbr ></wbr><wbr ></wbr>e("f12") & " - " & Sheets("Information").Rang<wbr ></wbr><wbr ></wbr>e("p24")
    .To = Sheets("Information").[L35<wbr ></wbr><wbr ></wbr>] ' <-- Put email of the recipient here
    '.CC = "..." ' <-- Put email of 'copy to' recipient here
    .HTMLbody = "<html><font face=Calibri><font size=4><p>" & Sheets("Information").Rang<wbr ></wbr><wbr ></wbr>e("f35") & "," & "<br>" & "<br>" _
         & "CHRISTMAS PARTIES" & Sheets("Information").Rang<wbr ></wbr><wbr ></wbr>e("f20") & "." _
         & "  ARE LOADS OF FUN." _
         & "<br>" & "<br>" & "Thank You," & "<br>" _
         & signature
          
    .Attachments.Add PdfFile
    
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
         
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OMail = Nothing
  Set OApp = Nothing
 

End Sub

Open in new window

0
Ryan ChongCommented:
I'm not too sure if .Display will eventually send out the email, but I can see you're deleting the pdf after .Display ?

(sorry as I don't have Outlook installed on my home laptop so I can't really test this as well as test the integrated scripts below. I will only come back to work next Mon)

you probably can incorporate my codes into your codes to become as follows:
Sub Procedure1()

  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OApp As Object, OMail As Object, signature As String
  
  ' Not sure for what the Title is
  Title = Range("A1")

  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & Sheets("Christmas").Name & ".pdf"
 
  ' Export activesheet as PDF
  With Sheets("Christmas")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
    With OMail
    .Display
    End With
        signature = OMail.HTMLbody
  If Err Then
    Set OApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
  
  ' Prepare e-mail with PDF attachment
  With OMail
   
    ' Prepare e-mail
    .Subject = "Christmas: " & Sheets("Information").Range("f12") & " - " & Sheets("Information").Range("p24")
    .To = Sheets("Information").[L35] ' <-- Put email of the recipient here
    '.CC = "..." ' <-- Put email of 'copy to' recipient here
    .HTMLbody = "<html><font face=Calibri><font size=4><p>" & Sheets("Information").Range("f35") & "," & "<br>" & "<br>" _
         & "CHRISTMAS PARTIES" & Sheets("Information").Range("f20") & "." _
         & "  ARE LOADS OF FUN." _
         & "<br>" & "<br>" & "Thank You," & "<br>" _
         & signature
          
    .Attachments.Add PdfFile
    
    'Add flag and reminder
    .MarkAsTask olMarkNextWeek
    .TaskStartDate = Date + 10
    .TaskDueDate = Date + 10
    .FlagRequest = "Reminder"
    .ReminderSet = True
    .ReminderTime = Date + 10
    
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
         
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OMail = Nothing
  Set OApp = Nothing

End Sub

Open in new window

on a separate note, there are some random invalid chars within your codes "<wbr ></wbr><wbr ></wbr>", you may take note of this...
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.