Code is getting hanged

[Jagdev Singh]

Hi Experts

I have the below code which supposed to save the list of emails in a draft folder. It works fine, but the excel file gets hanged after saving 12-13 mails in my draft folder. Could you please let me know what I am doing wrong here.

Sub Create_WCN_Statements()
Dim strFname As String
'Dim Statementdate As String

Application.DisplayAlerts = False
Workbooks("Macro.xlsm").Activate
Sheets("Email Database").Select
'Range("D2").Value = InputBox("Insert Date in MM/DD/YY Format for Statement, Email Body & Subject line")
'If Range("D2").Value = "" Then
    'Statementdate = Range("D3").Text
    'End If
   
Dim TheString As String, TheDate As Date
Dim Statementdate As String
Dim Emaildate As String

Range("D2").Select
Selection.ClearContents
Selection.NumberFormat = "[$-409]d-mmmmmm-yyyy;@"
Range("B5").Select
Selection.ClearContents
Selection.NumberFormat = "[$-409]mmmmmm yyyy;@"

TheString = Application.InputBox("Insert Date in DD/MM/YY Format for Statement, Email Body & Subject line")
If IsDate(TheString) Then
    TheDate = DateValue(TheString)
Else
    MsgBox "Invalid date"
    Exit Sub
End If
Range("D2") = TheDate
Range("D2").Select
Selection.NumberFormat = "[$-409]d-mmmmmm-yyyy;@"
Statementdate = Range("D2").Text

Range("B5") = TheDate
Range("B5").Select
Selection.NumberFormat = "[$-409]mmmmmm yyyy;@"
Emaildate = Range("B5").Text

Sheets("Agent ID").Select
Dim lastrow As Long

With ThisWorkbook.ActiveSheet
    R = .Range("A65536").End(xlUp).Row
    For a = 2 To R
    Sheets("WCN Raw Data").Select
    ActiveSheet.Range("A:S").AutoFilter Field:=16, Criteria1:=.Cells(a, 1), Operator:=xlAnd
    Workbooks.Open ("I:\WCN & WREP Statements Macro\Statement Templates\WCN Statement Template.xlsx")
    Workbooks("Macro.xlsm").Activate
    Sheets("WCN Raw Data").Select
    lastrow = Sheets("WCN Raw Data").Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").Select
    Range("A1:S1" & lastrow).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Windows("WCN Statement Template.xlsx").Activate
    Sheets("WCN_Statement").Select
    Range("B12").Select
    ActiveSheet.Paste
    Range("B12").Select
    Rows("12:12").Select
    Selection.Delete Shift:=xlUp
    Range("B12").Select
    strFname = Range("R12").Text
       
        FPath = "I:\WCN & WREP Statements Macro\Statements\WCN\"
                strNomeFicheiro = Range("R12") & " - " & Range("Q12").Value
               
                strNomeFicheiro = Replace(strNomeFicheiro, "/", " ")
                For j = 1 To 31
                strNomeFicheiro = Replace(strNomeFicheiro, Chr(j), "")
                Next j
               
        ActiveWorkbook.SaveAs Filename:=FPath & strNomeFicheiro & ".xlsx"
    Application.Run "Macro.xlsm!Macro5"
    Range("G2").Select
    Range("G2").Value = "Account Detail as at - " & Statementdate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Windows("Macro.xlsm").Activate
    Sheets("WCN Raw Data").Select
    ActiveSheet.AutoFilterMode = False
    Range("A1").Select
    Sheets("Agent ID").Select
    Sheets("Email Database").Select
    Range("B1").Select
    ActiveCell.Formula = strFname
    Range("B6").Select
    ActiveCell.Formula = strNomeFicheiro
    Application.Run "Macro.xlsm!Email_WCN"
    Windows("Macro.xlsm").Activate
    Sheets("Agent ID").Select
    Next a
End With
    Sheets("Email Database").Select
    Range("D2").Select
    Selection.ClearContents
    Sheets("Agent ID").Select
    Range("A1").Select
MsgBox "Done"
Application.DisplayAlerts = True
End Sub

Regards,

JD

 

[NARAYANK991]

Hi ,

Not really related to your question , but these two links have a lot of information on the uses of Call vs. Run.

http://www.mrexcel.com/forum/excel-...excel-visual-basic-applications-call-run.html

http://www.vbaexpress.com/forum/showthread.php?8948-Run-or-Call

Narayan

 

[Jagdev Singh]

Hi Narayan

Thanks for the links, but I am still unclear what I am doing wrong in the above code and why my excel is getting hanged...

Regards,
JD

 

[NARAYANK991]

Hi ,

As usual , I can only say that only a VBA master may be able to read code and debug it , especially when you say the problem happens after some executions.

If you can upload a complete workbook , with the code in it , and with the worksheet structures , data layouts , it might make debugging a little easier.

Narayan

 

[Jagdev Singh]

Hi Narayan

It contains list of excel files and sample mail templates and the macro sheet. What should I upload for your better understanding?

Regards,

JD

 

[Deepak]

Can we the parent xl as well.

 

[NARAYANK991]

Hi Jaggi ,

You are now a seasoned member of this forum , and should be aware that a complete specification of a problem has a better chance of getting resolved.

Asking others to debug code , especially when a complete specification of what the code is supposed to do is not given , means a long wait for a solution. If you are prepared to wait , please do so.

I do not know what you should upload ; however , going by what your code is doing , Excel workbooks are involved ; files are opened , closed , saved , ranges are copied and pasted. You need to decide what is to be uploaded.

You have not posted the code for the macro Macro.xlsm!Email_WCN ; putting in two Debug.Print commands in that macro , one at the beginning and the other at the end may help. You have not mentioned what happens after the program execution freezes ; what do you do ?

All troubleshooting is a matter of having the maximum possible useful information available ; in the absence of this , troubleshooting is more a matter of experience and / or guesswork rather than intelligent analysis.

Narayan