Outlook and excel (not sending mail)

K

Killavirus

Board Regular
Joined
Sep 21, 2004
Messages
187
Hi all

Wow long time since Ive been here :D
ahh the pleasures i can remember of using 97 with no wildcards !!!! lol

anyway i have set myself up for the impossible again.

looking to outlook and excel communicating

when backup finishes successfully it sends me an email (done after headaches still some there with authentication with symantec 10d which doesn't support authentication GRRRRR)

when that email is received by outlook it informs excel sheet there is a email from ......@...com

then excel displays a tick for the day or a color depending on when email was last received.
not worried about color or much just if i can display something is there and the formating of how its displayed i can sort later.

prolly use conditional formatting but thats not a major worry tbh

I am more than happy to just be pointed in the right direction so i can also understand a bit how its gonna work :D

cheers all in advance

p.s. sorry i posted on another board because at the time i could remember the name or mr excel ... Sorry .... not trying to spam the net
*edit
sorry new rules request the other link :D
http://www.excelforum.com/excel-programming/689955-monitor-email.html
 
Last edited:
Sort by date Sort by votes
ok i have been able to do this by
using rules to trigger a script
Code: 
Public Sub my_trigger(MyMail As MailItem)
Dim XLApp As Object ' Excel.Application
Dim xlWkb As Object ' Excel.Workbook
Set XLApp = CreateObject("Excel.Application") ' New Excel.Application
Set xlWkb = XLApp.Workbooks.Open("G:\lionel\montior\companies\m-l.xls")
XLApp.ScreenUpdating = False
    On Error GoTo 0
    XLApp.Visible = True
    xlWkb.Save
    XLApp.Quit
    XLApp.ScreenUpdating = True
Set XLApp = Nothing
Set xlWkb = Nothing
End Sub
Which opens a workbook which then inserts the date on the weekdays i have entered
Code: 
Private Sub Workbook_Open()



If Weekday(Date) = vbSunday Or Weekday(Date) = vbSaturday Then
    Cells.Find(What:="weekend", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        Selection.Offset(1).Value = Format(Date, "mm-dd-yyyy")
    With Selection.Offset(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Else

End If

If Weekday(Date) = vbMonday Then
    Cells.Find(What:="monday", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        Selection.Offset(1).Value = Format(Date, "mm-dd-yyyy")
    With Selection.Offset(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else

End If

If Weekday(Date) = vbTuesday Then
        
        Cells.Find(What:="tuesday", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
        Selection.Offset(1).Value = Format(Date, "mm-dd-yyyy")
    With Selection.Offset(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    



End If

If Weekday(Date) = vbWednesday Then
    Cells.Find(What:="Wednesday", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        Selection.Offset(1).Value = Format(Date, "mm-dd-yyyy")
    With Selection.Offset(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else

End If


If Weekday(Date) = vbThursday Then
    Cells.Find(What:="thursday", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        Selection.Offset(1).Value = Format(Date, "mm-dd-yyyy")
    With Selection.Offset(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else

End If


If Weekday(Date) = vbFriday Then
    Cells.Find(What:="friday", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        Selection.Offset(1).Value = Format(Date, "mm-dd-yyyy")
    With Selection.Offset(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else

End If




End Sub

and then i have a third workbook to gather the results from all the different workbooks
(i could of used just 1 workbook on multiple sheets but felt this would produce less errors)
Code: 
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    
    Application.ScreenUpdating = False
    
    
Set wb = Workbooks.Open("G:\lionel\montior\companies\M-L.xls", True, True)
    Range("A1:F2").Select
    Selection.Copy
    wb.Close False
    Range("c10").Select
    ActiveSheet.Paste
    Set wb = Nothing ' free memory
    Range("a1").Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

drom
  • Question Question
Send Mail using Gmail and Display
Replies
1
Views
95
Logit
Logit
E
  • Question Question
Send Range of Cells to Body Of Outlook 365 Without Crashing
2
Replies
10
Views
470
ExcelVBAnovice
E
B
  • Question Question
Is there any way to processing the data in the mail to Excell
Replies
1
Views
99
rowebca
rowebca
Roland Hoelscher
  • Question Question
Send emails from Excel using my Gmail account
Replies
2
Views
134
rollis13
rollis13
Jemini Jimi
  • Question Question
Excel VBA to email multiple worksheets as PDFs
Replies
8
Views
390
DanteAmor
DanteAmor

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top