'Code not execute in all pc for assign email address from excel cell
I have created a tool to automate require data emailed to the concern users. Data is in one sheet and reference of Location and email address is in second sheet
Private Sub CommandButton1_Click()
Dim EmailApp As Object
Set EmailApp = CreateObject("Outlook.Application") 'Object for Outlook
Dim NewEmailItem As Object
For r = 2 To Worksheets("Receipant").Cells(Worksheets("Receipant").Rows.Count, Range("C2").Column).End(xlUp).Row
FltrCrit1 = Sheets("Receipant").Cells(r, 3)
Workbooks.Add
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FltrCrit1 & ".xlsx"
Set wbNewWorkbook = Workbooks.Open(ThisWorkbook.Path & "\" & FltrCrit1 & ".xlsx")
ThisWorkbook.Activate
ThisWorkbook.Sheets("Data").Select
ActiveSheet.Range("$A$4:$p$" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=1, Criteria1:=FltrCrit1
ActiveSheet.Range("$A$4:$p$" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
wbNewWorkbook.Activate
wbNewWorkbook.ActiveSheet.Paste
wbNewWorkbook.Save
wbNewWorkbook.Close
Set NewEmailItem = EmailApp.CreateItem(0) 'Object for mail within Outlook
NewEmailItem.To = Sheets("Receipant").Cells(r, 1)
But in few Systems following error generated at the line number 20. i.e. NewEmailItem.To = Sheets("Receipant").Cells(r, 1)
Run-time error '-2147417851 (80010105)': Automation error The server threw an exception.
And when I remove the sheets reference and input the literal text of email address it will execute without any error. E.g. NewEmailItem.To = “[email protected]”
I could not getting why few users facing error of the same tool even os and office version are same.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
