Excel faylida har kuni kiruvchi elektron pochta xabarlarining umumiy sonini qanday avtomatik yozib olish mumkin

Ushbu maqola sizga Outlook-ga har kuni qancha xat olganingizni avtomatik ravishda hisoblash va raqamni Excel fayliga yozish imkonini berishni o'rgatadi.

Ko'pgina foydalanuvchilar har kuni qabul qilingan elektron pochta xabarlarining umumiy sonini hisoblashlari kerak. Bundan tashqari, kelajakda tekshirish qulayroq bo'lishi uchun ko'pchilik Excel faylida umumiy hisobni yozib olishga odatlangan. Bunday holda, siz har kuni qo'lda hisoblash va yozishni tanlashingiz mumkin. Biroq, bu biroz muammoli. Va ba'zida buni qilishni unutishingiz mumkin. Shuning uchun, Outlookni avtomatik ravishda amalga oshirishga imkon beradigan qulay usulni xohlashingiz kerak. Ushbu talabga javoban biz sizga VBA dan qanday foydalanishni uni amalga oshirishni quyidagi bosqichlarda o'rgatamiz.

Excel faylida har kuni kiruvchi elektron pochta xabarlarining umumiy sonini avtomatik yozib oling

1. Eng boshida Outlook ilovasini ishga tushiring.
2. Keyin asosiy Outlook oynasida "Alt + F11" tugmachalarini bosing.
3. Qalqib chiquvchi VBA muharriri oynasida "ThisOutlookSession" loyihasini oching.
4. Keyinchalik, ushbu loyihaga quyidagi VBA kodlaridan nusxa ko'chiring va joylashtiring.

Private Sub Application_Reminder(ByVal Item As Object)
    If Item.Class = olTask And Item.Subject = "Update Email Count" Then
       Call GetAllInboxFolders
    End If
End Sub

Private Sub GetAllInboxFolders()
    Dim objInboxFolder As Outlook.Folder
    Dim strExcelFile As String
    Dim objExcelApp As Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim nNextEmptyRow As Integer
    Dim lEmailCount As Long
 
    lEmailCount = 0
    Set objInboxFolder = Outlook.Application.Session.GetDefaultFolder(olFolderInbox)
    Call UpdateEmailCount(objInboxFolder, lEmailCount)
 
    ‘Change the path to the Excel file
    strExcelFile = "E:\Email\Email Count.xlsx"
    Set objExcelApp = CreateObject("Excel.Application")
    Set objExcelWorkbook = objExcelApp.Workbooks.Open(strExcelFile)
    Set objExcelWorksheet = objExcelWorkbook.Sheets("Sheet1")
 
    nNextEmptyRow = objExcelWorksheet.Range("A" & objExcelWorksheet.Rows.Count).End(xlUp).Row + 1
 
    'Add the values into the columns
    objExcelWorksheet.Range("A" & nNextEmptyRow) = nNextEmptyRow - 1
    objExcelWorksheet.Range("B" & nNextEmptyRow) = Year(Date - 1) & "-" & Month(Date - 1) & "-" & Day(Date - 1)
    objExcelWorksheet.Range("C" & nNextEmptyRow) = lEmailCount
 
    'Fit the columns from A to C
    objExcelWorksheet.Columns("A:C").AutoFit
   
    'Save the changes and close the Excel file
    objExcelWorkbook.Close SaveChanges:=True
End Sub

Private Sub UpdateEmailCount(objFolder As Outlook.Folder, ByRef lCurEmailCount As Long)
    Dim objItems As Outlook.Items
    Dim objItem As Object
    Dim objMail As Outlook.MailItem
    Dim strDay As String
    Dim strReceivedDate As String
    Dim lEmailCount As Long
    Dim objSubFolder As Outlook.Folder
 
    Set objItems = objFolder.Items
 
    objItems.SetColumns ("ReceivedTime")
    strDay = Year(Date - 1) & "-" & Month(Date - 1) & "-" & Day(Date - 1)
 
    For Each objItem In objItems
        If objItem.Class = olMail Then
           Set objMail = objItem
           strReceivedDate = Year(objMail.ReceivedTime) & "-" & Month(objMail.ReceivedTime) & "-" & Day(objMail.ReceivedTime)
           If strReceivedDate = strDay Then
              lCurEmailCount = lCurEmailCount + 1
           End If
        End If
    Next
 
    'Process the subfolders in the folder recursively
    If (objFolder.Folders.Count > 0) Then
       For Each objSubFolder In objFolder.Folders
           Call UpdateEmailCount(objSubFolder, lCurEmailCount)
       Next
    End If
End Sub

5. Keyin, ushbu kodni imzolang va imzolangan makroslarga ruxsat berish uchun Outlook makro sozlamalarini o'zgartiring.
6. Shundan so'ng siz har kuni takrorlanadigan vazifani yaratishingiz kerak.

• Birinchidan, Vazifalar panelidagi "Yangi vazifa" tugmasini bosing.
• Qalqib chiquvchi "Yangi vazifa" oynasida "Takrorlanish" tugmasini bosing.
• Keyin keyingi dialog oynasida "Kundalik", "Har 1 kun (lar)" va "Yakuniy sana yo'q" ni tanlang va nihoyat "OK" tugmasini bosing.
• Keyinroq vazifa mavzusini va eslatmani ehtiyojlaringizga qarab o'zgartiring.
• Oxir-oqibat "Saqlash va yopish" tugmasini bosing.

7. Bundan buyon, har safar ushbu vazifani eslatuvchi ogohlantirishlar kelganda, Outlook kecha olingan elektron pochta xabarlarini avtomatik ravishda hisoblab chiqadi va keyin quyidagi skrinshot kabi raqamni Excel fayliga yozib oladi:

Doimiy PST ma'lumotlarini yo'qotishdan qochish

Hech kim doimiy PST ma'lumotlarini yo'qotishni qabul qilishga tayyor emas. Biroq, Outlook PST fayli buzilishlarga moyil. Shuning uchun, siz PST ma'lumotlarini doimiy va yangilangan zaxira nusxasini yaratish va kuchli saqlash kabi ehtiyot choralarini ko'rishingiz kerak. PSTni tiklash yaqin atrofdagi asbob, kabi DataNumen Outlook Repair.

Muallif kirish:

Shirli Chjan ma'lumotlarni qayta tiklash bo'yicha mutaxassis DataNumenMa'lumotlarni qayta tiklash texnologiyalari bo'yicha jahon yetakchisi bo'lgan , Inc sql ta'mirlash va Outlook ta'mirlash dasturiy mahsulotlar. Qo'shimcha ma'lumot olish uchun tashrif buyuring www.datanumen.com