code to 'put' calc displayed in message box in cells

S

shaz0503

All

Apologies about the title - hard to describe

I have borrowed a terrific bit of code to count emails in specific folders;
however the count result is only displayed in a message box - how do i get
the result 'put' to a specific cell.
 
G

Gord Dibben

Without seeing the code.................shrug shoulders<g>

Your code does a count and that count is displayed in a msgbox.

Just redirect the count to a cell like count = Range("A1").Value

Example code.............

Sub sheets_num()
MsgBox ThisWorkbook.Sheets.Count 'will display in msgbox
Range("A1").Value = ThisWorkbook.Sheets.Count 'will be entered in A1
End Sub


Gord Dibben MS Excel MVP
 
B

Barb Reinhardt

If you're working on the activesheet, do something like this

=activesheet.range("C1").value = ... enter your value
 
S

shaz0503

Gord

thanks heaps for this - I have included the code (minus your code) for you -
have tried your suggestion but am obviously doing something wrong with
editing code to 'put' value in msg box in cell d1.

Again many thanks to those who have helped with this (includes those from
other posts)

shaz



Sub HowManyDatedEmails_S()
Dim objOutlook As Object, objnSpace As Object, objFolder As Object
Dim EmailCount As Long
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")

On Error Resume Next
Set objFolder = objnSpace.Folders("Mailbox - Sharon
Hickox").Folders("Personal")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder."
Exit Sub
End If


Dim iCount As Integer
Dim DateCount As Integer
Dim myFirstDate As Date
Dim myLastDate As Date

EmailCount = objFolder.Items.Count
DateCount = 0
myFirstDate = Sheets("Sheet1").Range("A1").Value
myLastDate = Sheets("Sheet1").Range("A2").Value

For iCount = 1 To EmailCount
With objFolder.Items(iCount)
If DateSerial(Year(.ReceivedTime), _
Month(.ReceivedTime), _
Day(.ReceivedTime)) >= myFirstDate And _
DateSerial(Year(.ReceivedTime), _
Month(.ReceivedTime), _
Day(.ReceivedTime)) <= myLastDate Then
DateCount = DateCount + 1
End If
End With
Next iCount


Set objFolder = Nothing
Set objnSpace = Nothing
Set objOutlook = Nothing

MsgBox "Number of emails in Personal folder with matching date: " &
DateCount, , "Personal date count"


End Sub
 
S

shaz0503

Gord and Barb

with some fiddling I got it to do exaclty what I wanted...yeh...

ended up using Barb's code

thanks heaps

Shaz
 
G

Gord Dibben

After or replacing the line.....................

MsgBox "Number of emails in Personal folder with matching date: " & _
DateCount, , "Personal date count"

Add this line................................

ActiveSheet.Range("D1").Value = "Number of emails etc. is " & DateCount


Gord
 
Top