Skip to main content

Mail_ReceivedOrNot_Mailed मेल प्राप्त हुआ या नहीं मिला

 

Mail_ReceivedOrNot_Mailed

Formula Used:-

="\\pgarg\Pk_Sharing_Folder\OutlookSaveDSRAttachment\Delhi_Ncr\"&TEXT(Q1,"DD-MM-YYYY")&"\"&"*"

=IFERROR(@INDEX(List_Delhi_NCR,ROW()-2),"")







Sub SetReminderMail_Delhi_NCR()

Dim Sdate As String

Dim sWorkbook As Workbook

Dim sWorkbook1 As String

Dim sWorkbook2 As String

Dim wb As Workbook

Dim objApp As Object

Dim ws As Worksheet

Dim destWB As Workbook

Dim Path As String

Dim sh As Object

        

        Application.DisplayAlerts = False

        Application.ScreenUpdating = False

'Delete all file from folder temp2

        On Error Resume Next

        Kill "E:\Pkgarg_Record\Pkgarg_Final_Cril_Report\temp\*.*"

        On Error GoTo 0

'Process start

        On Error Resume Next

        Set wb = Workbooks("\Mail_ReceivedOrNot_Mailed\Mail_ReceivedOrNot_Mailed.xlsm")    'Current File

        Worksheets("Mail_Reminder").ShowAllData

        Worksheets("Mail_Reminder").RefreshAll

        On Error GoTo 0

        If Not wb Is Nothing Then

        MsgBox "It's open"

       

        'Do not have to open

        Else

        ChDir "C:\"

        Set wb = Workbooks.Open(Filename:="\Mail_ReceivedOrNot_Mailed\Mail_ReceivedOrNot_Mailed.xlsm")

        Worksheets("Mail_Reminder").AutoFilterMode = False

        wb.Activate

        End If

            



'AutoFilter Proccess

wb.Activate

'sWorkbook1 = "\MASTER_OF_OUTSTATION.xlsm"

Application.ScreenUpdating = False

ActiveSheet.AutoFilterMode = False

wb.Activate

Worksheets("Delhi_Working").AutoFilterMode = False

Worksheets("Delhi_Working").Range("A1:H100").Clear

'Sdate = Sheets("controlsheet").Range("h1")

wb.Activate

Worksheets("Mail_Reminder").Activate

Range("D2").Select

    ActiveSheet.Range("$A$2:$O$300").AutoFilter Field:=4, Criteria1:= _

        "CANTABIL DELHI NCR"

    Range("K2").Select

    ActiveSheet.Range("$A$2:$O$300").AutoFilter Field:=11, Criteria1:="0"

    Selection.End(xlToLeft).Select

    Range("A2:H2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

wb.Activate

Worksheets("Delhi_Working").Activate

With Worksheets("Delhi_Working").Range("A1")

.PasteSpecial xlPasteValues

.PasteSpecial xlPasteFormats

Selection.Columns.AutoFit

Application.CutCopyMode = False

 End With


        'Yha tak clear hai


Dim Fname As String

Dim i As Long

Dim signature As String


'On Error Resume Next

i = 1

Fname = Sheets("Delhi_Working").Cells(i + 1, 6).Value


Do While Fname <> Empty

wb.Activate

Fname = Sheets("Delhi_Working").Cells(i + 1, 6).Value


Application.ScreenUpdating = False

Application.DisplayAlerts = False




If Fname = "" Then

wb.Activate

MsgBox "Delhi_Ncr Mail Process Done!"

'ActiveWorkbook.Close savechanges:=False


Exit Sub

Else

If Not IsEmpty(Fname) Then

End If

End If



Dim store As String

Dim bmsg As String

Dim ML As Long

'bmsg = Sheets("controlsheet").Cells(i + 1, 1).Offset(0, 5).Value

store = Fname


Application.DisplayAlerts = False

Application.ScreenUpdating = False




'With Worksheets("Delhi_Working").Range("$A$1:$c$180")

'.AutoFilter Field:=6, Criteria1:="=" & 0

'.AutoFilter Field:=2, Criteria1:=Fname

 'End With


wb.Activate

Worksheets("Delhi_Working").Activate


'With Worksheets("Delhi_Working").Range("$A$1:$c$180")

'.AutoFilter Field:=6, Criteria1:="=" & 0

'.AutoFilter Field:=2, Criteria1:=Fname

 'End With

'Worksheets("Delhi_Working").Activate

'Worksheets("Delhi_Working").Range("A2").CurrentRegion.Select

'Worksheets("Delhi_Working").Range("A2").CurrentRegion.Copy


Application.ScreenUpdating = False

Application.DisplayAlerts = False


'Set obj = Workbooks.Add

'obj.Activate


'Paste the that copy from target file.

'With obj.Worksheets("Sheet1").Range("A1")

'.PasteSpecial xlPasteValues

'.PasteSpecial xlPasteFormats

'Selection.Columns.AutoFit

' End With


 

'Autofit the selection and delete the column


'savepath = ".xlsx": FileFormatNum = 5

'ActiveWorkbook.SaveAs Filename:="\temp2\" & Fname & savepath, FileFormat:=51

'ActiveWorkbook.Close savechanges:=True


'Set sh = Workbooks.Open(Filename:="\temp\" & Fname & savepath)


wb.Activate

Worksheets("Delhi_Working").Activate

'--for mail --

Select Case Fname

Case Fname

Dim outlookApp As Object

Dim OutlookMail As Object

Dim xlnspect As Object

Dim pageEditor As Object

Dim b As String

Dim c As String


Set outlookApp = CreateObject("Outlook.Application")

Set OutlookMail = outlookApp.CreateItem(0)


b = Worksheets("Delhi_Working").Cells(i + 1, 1).Offset(0, 10).Value

c = Worksheets("Delhi_Working").Cells(i + 1, 1).Offset(0, 5).Value

'On Error Resume Next

With OutlookMail

        

     .To = Worksheets("Delhi_Working").Cells(i + 1, 1).Offset(0, 5).Value

    .CC = Worksheets("Delhi_Working").Cells(i + 1, 1).Offset(0, 10).Value

    .Subject = "Reminder!!!! Send Your Pending Store DSR of Today, If You Have Sent Pls. Ignore It"

    .Body = "Hi" & " " & "," & vbNewLine & vbNewLine & "Reminder!!!!Store DSR did not receive till now" & vbNewLine & vbNewLine & vbNewLine & "Regards" & vbNewLine & "MIS Team"

       

    ' ActiveWorkbook.Save

    '.Attachments.Add "\temp2\" & Fname & savepath

    '.Attachments.Add ("\Desktop\PDf file\" & store & ".PDF")

    '.Display

    'Application.Wait (Now + TimeValue("00:00:08"))

    .send


End With

Set OutlookMail = Nothing

Set outlookApp = Nothing



'Application.Wait (Now + TimeValue("00:00:15"))



End Select

i = i + 1


Loop



End Sub

Comments

Post a Comment

Your advice or suggestions will be much appreciated and welcomed....

Popular posts from this blog

What is Excel ? एक्सेल क्या है ?

Excel is a spreadsheet application which have rows and columns to organize data. We can easily organize and analysis data in excel. एक्सेल एक स्प्रेडशीट एप्लिकेशन है जिसमें डेटा व्यवस्थित करने के लिए पंक्तियां और कॉलम हैं। हम एक्सेल में आसानी से डेटा व्यवस्थित और विश्लेषण कर सकते हैं।
16 comments
Read more

VBA Code to Clear Cells with Zero शून्य के साथ सेल को साफ़ करने के लिए VBA कोड

# VBA Code to Clear Cells with Zero:- To  clear cells with zero within a cell range using VBA , use a macro with the following statement structure: शून्य के साथ सेल को साफ़ करने के लिए VBA कोड For   Each   Cell  In   Range If   Cell.Value = myValue  Then   Cell.Clear Next   Cell VBA Statement Explanation Lines #1 and #3: For Each Cell In Range | Next Cell Item:  For Each… In… Next. VBA Construct:  For Each… Next statement. Description:  The For Each… Next statement repeats the statement within the loop (line #2) for each element (Cell) in the cell range (Range) you want to search for zeroes in. Item:  Cell. VBA Construct:  Element of the For Each… Next statement and object variable of the Range object data type. Description:  The Element of the For Each… Next statement is an object variable used to iterate through the elements (Cell) of the cell range (Range) you want to sear...
Post a Comment
Read more

VBA Coding : For Sharing data by Email With Attachment using Excel data in other Place

' We have some data in Excel that we want to share by email with attachment हमारे पास Excel में कुछ डेटा हैं जिन्हें हम अनुलग्नक के साथ ईमेल द्वारा साझा करना चाहते हैं Sub For_GSTR3B_Report_Share() Dim sWorkbook1 As Workbook Dim wb As Workbook Dim objApp As Object  'For New Workbook Dim ws As Worksheet Dim savepath As String         'Delete all file from folder temp2         On Error Resume Next         Kill "C:\Users\PRAMOD.GARG\Desktop\pk_temp*.*"         On Error GoTo 0               On Error Resume Next         Set wb = Workbooks("Pkgarg_Control_Sheet_With_Gaurav_Coding.xlsm")         On Error GoTo 0         If Not wb Is Nothing Then         MsgBox "It's open"         wb.Activate         Worksheets("For_GSTR3B_Report_Share...
Post a Comment
Read more