Excel bị lỗi sau khi gửi email bằng CDO

[MinhKhai]

Nhờ các anh chị kiểm tra giúp lỗi này là lỗi gì với ạ. Em không biết mô tả lỗi này như thế nào cho đúng. Các anh chị xem Clip và File đính kèm để biết rõ hơn

Do file load dữ liệu từ SQL Server, em xin mô tả lỗi như trong Clip như sau:
- Bình thường khi em mở file, bấm Spin Button thì dữ liệu được load như ý muốn.
- Khi em bấm nút Send Email như trong hình, 1 email có được gửi đi đúng địa chỉ (em đã bỏ phần đính kèm file). Lúc này Excel có vẻ treo, hoặc lúc này bấm Spin Button dữ liệu vẫn load ra (xem trên Formular bar vẫn có) nhưng trong ô thì không hiện rõ chữ. Không phải các ô đều không hiện mà có vài ô hiện chữ và vài ô không.
Em đã dò từng dòng code, kết hợp với Google mà không tìm ra lý do cũng như cách khắc phục nó. Em cũng đã thử bỏ 1 số dòng lệnh không liên quan nhưng không có kết quả.
Rất mong các anh chị mách nước giúp

Trân trọng cảm ơn

 

[befaint]

Khai báo thế này thì chỉ có chị U được khai báo là String thôi, các anh còn lại là Variant hết. Cứ tưởng kiểu khai báo này chỉ có ở mấy cái clip quảng cáo. @@

Public B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U As String

lỗi

Tìm mấy chỗ...

Application.ScreenUpdating  
Application.EnableEvents

 

[MinhKhai]

Khai báo thế này thì chỉ có chị U được khai báo là String thôi, các anh còn lại là Variant hết. Cứ tưởng kiểu khai báo này chỉ có ở mấy cái clip quảng cáo. @@

Public B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U As String

Cái này dùng cho mục đích đưa dữ liệu vào SQL Server, thực tế trong file không có tác dụng. Mình đã bỏ đoạn khai báo đó đi. Cảm ơn bạn đã chỉ lỗi khai báo.

Tìm mấy chỗ...

Application.ScreenUpdating 
Application.EnableEvents

Mình đã THÊM/BỚT lần lượt từng dòng như trên, thêm cả bỏ cả những dòng không liên quan chỉ tập trung mỗi việc gửi email mà vẫn bị vậy.

 

[befaint]

vẫn bị vậy

Vì nó dính vào lỗi cái này đè cái khác.
Muốn thử nghiệm thì bạn xóa hết các dòng đó đi xem..

 

[MinhKhai]

Vì nó dính vào lỗi cái này đè cái khác.
Muốn thử nghiệm thì bạn xóa hết các dòng đó đi xem..

Mình đã xóa mọi dòng không liên quan. Trong VBE, đã dò từng dòng code bằng cách bấm F8 rất nhiều lần để xem nó thực thi những lệnh nào. Thực tế nó chỉ chạy cái Sub để gửi mail thôi.
Tuy nhiên mình có thấy hiện tượng:
- Nếu bấm nút lệnh Send Email thì Excel nó bị hiện tượng đơ như đã nói bên trên
- Nếu thực thi trong VBE bằng cách bấm nhiều lần F8 cho đến khi thực thi hết lệnh thì lại không bị làm sao.

Thật là khó hiểu

 

[gttrongvn]

Bạn thử giải pháp thay các actvieX controls bằng shape xem. Với bạn chuyển code kiểu này .[I7] = Sheet2.[BA1] sang .range("I7").value=Sheet2.range("BA1").value thử, trước mình cũng thích viết tắt kiểu này với .range("I7")=Sheet2.range("BA1"), nhưng khi xài bên office2010 thì bình thường, sang office2013 thì bị lỗi, mình phải khai báo đầy đủ ( .Text hay .Value mình quên rồi) thì mới hết bị lỗi trên office2013, từ đó mình không viết tắt theo kiểu như vậy nữa.

 

[ongke0711]

Có 2 hằng "Connstr" thì biết dùng hằng nào cho hàm KetNoiServer.

 

[levanduyet]

Nhờ các anh chị kiểm tra giúp lỗi này là lỗi gì với ạ. Em không biết mô tả lỗi này như thế nào cho đúng. Các anh chị xem Clip và File đính kèm để biết rõ hơn

Do file load dữ liệu từ SQL Server, em xin mô tả lỗi như trong Clip như sau:
- Bình thường khi em mở file, bấm Spin Button thì dữ liệu được load như ý muốn.
- Khi em bấm nút Send Email như trong hình, 1 email có được gửi đi đúng địa chỉ (em đã bỏ phần đính kèm file). Lúc này Excel có vẻ treo, hoặc lúc này bấm Spin Button dữ liệu vẫn load ra (xem trên Formular bar vẫn có) nhưng trong ô thì không hiện rõ chữ. Không phải các ô đều không hiện mà có vài ô hiện chữ và vài ô không.
Em đã dò từng dòng code, kết hợp với Google mà không tìm ra lý do cũng như cách khắc phục nó. Em cũng đã thử bỏ 1 số dòng lệnh không liên quan nhưng không có kết quả.
Rất mong các anh chị mách nước giúp

Trân trọng cảm ơn

Hi em,
Em nên đưa vào bẫy lỗi cho các thủ tục.

Sub KetNoiServer(DataQuery As String)

    On Error GoTo KetNoiServer_Error
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = ConnStr
    Conn.CursorLocation = adUseClient
    'Nên đưa thêm vào ConnectionTimeOut để sau 1 thời gian kết nối không được thì thoát
    Conn.ConnectionTimeout = 15   
    Conn.Open

    Set rst = New ADODB.Recordset
    rst.ActiveConnection = Conn
    rst.Open Source:=DataQuery

    rng.CopyFromRecordset rst                                                                      'Truoc do da set Rng cho dia chi cu the
    ' Khi đóng kết nối cần phải kiểm tra trước
    ' Nếu kết nối đã đóng mà dùng lệnh Conn.Close thì sẽ gây ra lỗi
    If CBool(Conn.State And adStateOpen) Then Conn.Close


KetNoiServer_Exit:
    ' Giai phong bien va thuc hien cac phuong thuc can thiet truoc khi xoa
    Set Conn = Nothing
    Exit Sub

KetNoiServer_Error:
    ' Thong bao loi cho nguoi dung
    ' Khi su dung thong bao tieng Viet nay can them module ho tro thong bao Tieng Viet, tôi có đính kèm file
    MsgBoxUni VNI("Coù loãi xaõy ra, maõ loãi laø: ") & vbCrLf & Err.Number & " (" & Err.Description & ")", vbOKOnly + vbInformation, VNI("Thoâng baùo")
    Resume KetNoiServer_Exit:
End Sub

Ngoài ra cũng cần bẫy lỗi ở các thủ tục khác.
Theo tôi nếu được nên chỉnh lại cấu trúc thủ tục KetNoiServer.
Viết lại dạng hàm, lúc đó sẽ bỏ phần đóng kết nối.

Function KiemTraKetNoi() As Double
    ' Ham se tra ve 1 neu ket noi thanh cong
    '            va -1 neu ket noi khong thanh cong
    On Error GoTo KetNoiServer_Error
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = ConnStr
    Conn.CursorLocation = adUseClient
    Conn.ConnectionTimeout = 15
    Conn.Open
    ' Neu ket noi thanh cong thi ham se tra ve 1
    KiemTraKetNoi = 1

KetNoiServer_Exit:
    ' Giai phong bien va thuc hien cac phuong thuc can thiet truoc khi xoa

    Exit Function

KetNoiServer_Error:
    KiemTraKetNoi = -1
    Resume KetNoiServer_Exit:
End Function

Như vậy ở các thủ tục khác chỉ việc kiểm tra kết nối, nếu =1 thì thực hiện các thao tác khác, và cuối các thủ tục đó sẽ đóng kết nối.

Vài góp ý.

Lê Văn Duyệt

 

[MinhKhai]

Có 2 hằng "Connstr" thì biết dùng hằng nào cho hàm KetNoiServer.

Cảm ơn bạn đã nhắc. Mình vừa xóa 1 hằng và để lại 1 hằng có thông số kết nối chuẩn. Hiện tượng vẫn chưa được khắc phục. Bạn giúp thêm nhé

 

[levanduyet]

Có 2 hằng "Connstr" thì biết dùng hằng nào cho hàm KetNoiServer.

Nếu khai báo hằng ở từng module thì nó sẽ lấy ở từng module đó.
Nên đưa vào biến public để dùng chung.

Lê Văn Duyệt

 

[MinhKhai]

Cảm ơn bác đã góp ý. Bác hướng dẫn thêm giúp em nhé

Hi em,
Em nên đưa vào bẫy lỗi cho các thủ tục.

Sub KetNoiServer(DataQuery As String)
 
    ' Khi đóng kết nối cần phải kiểm tra trước
    ' Nếu kết nối đã đóng mà dùng lệnh Conn.Close thì sẽ gây ra lỗi
    If CBool(Conn.State And adStateOpen) Then Conn.Close

End Sub

Trong file em dùng lệnh kiểm tra dưới đây khác với của Bác thì đã ổn chưa ?

If rst.State <> 0 Then rst.Close
If Conn.State <> 0 Then Conn.Close

Ngoài ra cũng cần bẫy lỗi ở các thủ tục khác.
Theo tôi nếu được nên chỉnh lại cấu trúc thủ tục KetNoiServer.
Viết lại dạng hàm, lúc đó sẽ bỏ phần đóng kết nối.

Em chưa rõ cách dùng hàm này. Tức là từ thủ tục khác gọi đến hàm này. Hàm không có phần đóng kết nối, vậy khi load dữ liệu, thủ tục chạy từ đầu nó mở ra 1 kết nối khác có bị lỗi không? Ngoài ra việc không đóng kết nối ngay mà cho nó tự động sau 1 khoảng thời gian thì bộ nhớ có chiếm dụng không ?

Cảm ơn bác

Bài đã được tự động gộp: 15/6/20

Nếu khai báo hằng ở từng module thì nó sẽ lấy ở từng module đó.
Nên đưa vào biến public để dùng chung.

Lê Văn Duyệt

Bác cho hỏi: Khai biến ở trong Modul và trong sheet (phần đầu của sheet trong VBE) có khác nhau gì không?

 

[levanduyet]

Trong file em dùng lệnh kiểm tra dưới đây khác với của Bác thì đã ổn chưa ?

If rst.State <> 0 Then rst.Close
If Conn.State <> 0 Then Conn.Close

Như tôi đã nói ở trên:
If CBool(Conn.State And adStateOpen) Then Conn.Close

Em chưa rõ cách dùng hàm này. Tức là từ thủ tục khác gọi đến hàm này. Hàm không có phần đóng kết nối, vậy khi load dữ liệu, thủ tục chạy từ đầu nó mở ra 1 kết nối khác có bị lỗi không? Ngoài ra việc không đóng kết nối ngay mà cho nó tự động sau 1 khoảng thời gian thì bộ nhớ có chiếm dụng không ?

Cảm ơn bác

Bác cho hỏi: Khai biến ở trong Modul và trong sheet (phần đầu của sheet trong VBE) có khác nhau gì không?

Hàm này em viết dạng thế này

If KiemTraKetNoi=1 Then
'  Khi kết nối được thì thực hiện truy vấn, điền thông tin

Else
' Thông báo không kết nối được, kiểm tra lại các thông số kết nối

End If

Sau đó cuối thủ tục của bạn thì bạn sẽ đóng kết nối lại.

Lê Văn Duyệt

 

[MinhKhai]

Lẽ nào do cái Msgbox. Thật không thể hiểu nổi

Excel freezes after using macro that sends email using Collaboration Data Objects

I use a macro to send out emails, I am using this approach: https://qdatalab.com/vba-macro-send-emails-using-gmail/ It works, except that after the email is sent Excel will freeze. It is only the t...

stackoverflow.com

 

[ongke0711]

Lẽ nào do cái Msgbox. Thật không thể hiểu nổi

Nếu được thì bạn gửi cái Table với vài dòng dữ liệu lên đây, tôi chạy kiểm tra thử xem.

 

[ongke0711]

Nhờ các anh chị kiểm tra giúp lỗi này là lỗi gì với ạ. Em không biết mô tả lỗi này như thế nào cho đúng. Các anh chị xem Clip và File đính kèm để biết rõ hơn

Do file load dữ liệu từ SQL Server, em xin mô tả lỗi như trong Clip như sau:
- Bình thường khi em mở file, bấm Spin Button thì dữ liệu được load như ý muốn.
- Khi em bấm nút Send Email như trong hình, 1 email có được gửi đi đúng địa chỉ (em đã bỏ phần đính kèm file). Lúc này Excel có vẻ treo, hoặc lúc này bấm Spin Button dữ liệu vẫn load ra (xem trên Formular bar vẫn có) nhưng trong ô thì không hiện rõ chữ. Không phải các ô đều không hiện mà có vài ô hiện chữ và vài ô không.

Lỗi này liên quan đến CDO nhưng không phải do cái Msgbox mà do bạn chưa giải phóng các đối tượng CDO.
Sau khi Send xong bạn giải phóng nó luôn trước khi chạy các lệnh khác tương tác với Excel.

...
With EmailMsg
            .bodypart.Charset = "utf-8"
            Set .Configuration = EmailConf
            .To = Sh.[E22]
            .CC = Sh.[E25]
            .BCC = Sheet2.[C3]
            .From = """Hello"" <bao.ngquoc@gmail.com>"
            .Subject = Sh.[E14]
            '.AddAttachment AttFile
            .HTMLBody = "<B>" & Sheet2.[C4] & "</B>" & "<BR>" & _
                        Sheet2.[C5] & "<BR>" & Sheet2.[C6] & "<BR>" & "<BR>" & _
                        Sheet2.[C7] & "<BR>" & Sheet2.[C10] & "<BR>" & "<BR>" & "<B>" & _
                        Sheet2.[C13] & "</B>"
            .Send
        End With
        Set EmailMsg = Nothing
        Set EmailConf = Nothing
        Set EmailFields = Nothing

        Sh.[M23] = Now

        Call SQL.UpdateEmailSent    'Update lai gio gui
...

Bạn sửa và kiểm tra lại thử xem.

Còn một vấn đề khác bạn cần xem lại đó là cái nút Spin: cứ mỗi lần nhấn thì bạn lại tạo kết nối và lấy dữ liệu về 1 record thì nó phí phạm tài nguyên máy cho 1 lần kết nối quá. Tại sao bạn không lấy về 100 reocord rồi xử lý ở phía Client (Excel) thôi, không cần kết nối nữa. Nếu như mạng yếu chập chờn thì việc tải từng record một nó mất thời gian nhiều hơn không. Tại sao bạn không dùng một Listbox để hiển thị dữ liệu tải về từ SQL SV rồi duyệt trong Listbox thì nó tiện hơn.
Về cái userform để bạn chọn Email To, C/c thì bạn chỉ cần dùng 1 Form với 1 Combobox chọn Phòng ban + 1 Listbox để chọn địa chỉ email là được rồi. Dùng chung cho cả email To và C/c. Theo thiết kế tại của bạn thì chỉ thiết kế cho dữ liệu cố định như hiện tại chứ không tính trước cho sự thay đổi, nếu sau này có thêm Phòng ban mới, Email mới thì lại phải vô userform thiết kế lại???

 

[MinhKhai]

Lỗi này liên quan đến CDO nhưng không phải do cái Msgbox mà do bạn chưa giải phóng các đối tượng CDO.
Sau khi Send xong bạn giải phóng nó luôn trước khi chạy các lệnh khác tương tác với Excel.

...
With EmailMsg
            .bodypart.Charset = "utf-8"
            Set .Configuration = EmailConf
            .To = Sh.[E22]
            .CC = Sh.[E25]
            .BCC = Sheet2.[C3]
            .From = """Hello"" <bao.ngquoc@gmail.com>"
            .Subject = Sh.[E14]
            '.AddAttachment AttFile
            .HTMLBody = "<B>" & Sheet2.[C4] & "</B>" & "<BR>" & _
                        Sheet2.[C5] & "<BR>" & Sheet2.[C6] & "<BR>" & "<BR>" & _
                        Sheet2.[C7] & "<BR>" & Sheet2.[C10] & "<BR>" & "<BR>" & "<B>" & _
                        Sheet2.[C13] & "</B>"
            .Send
        End With
        Set EmailMsg = Nothing
        Set EmailConf = Nothing
        Set EmailFields = Nothing

        Sh.[M23] = Now

        Call SQL.UpdateEmailSent    'Update lai gio gui
...

Cảm ơn @ongke0711
Trước hết cảm ơn bạn đã bỏ thời gian tìm hiểu vấn đề của mình
Như nội dung Code, phần gửi email mình bỏ phần Call SQL.UpdateEmailSent nhưng khi chạy vẫn bị đơ như mô tả đầu tiên. Khi bấm F8 để xem Excel nó chạy lần lượt theo dòng lệnh nào thì sau khi chạy xong lệnh .SEND thì nó chạy thẳng đến phần giải phóng biến.

Tuy vậy việc bạn đưa lệnh giải phóng biến lên ngay sau lệnh .SEND đã gần như giải quyết vấn đề của mình. Nó vẫn cần vài lần bấm SpinButton nhưng sau đó dữ liệu đã Load như mong muốn. (Trước đó thì dù bấm bao nhiêu lần SpinButton cũng không tác dụng)

Đối với việc bấm SpinButton, đúng là mỗi lẫn bấm là 1 lần kết nối, nhưng ngay khi kết nối xong, nó ngắt kết nối và giải phóng biến thì vẫn chiếm dụng tài nguyên hả bạn ? Lý do Mình ko cho load 100 record ra Excel rồi xử lý trên là để dữ liệu trên Excel nó gọn và ko mất phần xử lý trên Excel nữa. (Do ban đầu mình nghĩ mở-đóng kết nối rồi giải phóng biến thì tài nguyên ko bị chiếm dụng)

Đối với 2 gợi ý của bạn (listbox và form nhập email) mình cũng nghĩ đến vấn đề này, nhưng là không biết cách làm nên cứ làm theo cách mình hiểu. (Một số thứ trong file là mình đi cóp nhặt trên diễn đàn này đó). Rất mong được bạn làm mẫu để mình hiểu cách làm

Một lần nữa cảm ơn bạn

 

[ongke0711]

Đối với việc bấm SpinButton, đúng là mỗi lẫn bấm là 1 lần kết nối, nhưng ngay khi kết nối xong, nó ngắt kết nối và giải phóng biến thì vẫn chiếm dụng tài nguyên hả bạn ? Lý do Mình ko cho load 100 record ra Excel rồi xử lý trên là để dữ liệu trên Excel nó gọn và ko mất phần xử lý trên Excel nữa. (Do ban đầu mình nghĩ mở-đóng kết nối rồi giải phóng biến thì tài nguyên ko bị chiếm dụng)

Ý tôi về tài nguyên máy ở đây không phải là chiếm bộ nhớ mà là tránh lãng phí thời gian, công sức cho 1 lần kết nối.
Ví dụ đơn giản như là bạn đi ra đầu hẻm mua cục nước đá về uống bia, mỗi lần đi mua 1 cục, uống xong mua tiếp cục khác, chạy ra chạy vô nhiều lần trong bữa nhậu thấy mệt không? Vậy sao không mua một bịch đá chừng 10.000 đ, đủ sức xách về, còn nếu mua bao đá 50.000 đ, xách về ì ạch, hết sức uống bia.
Còn việc load recordset từ SQL SV về thì nó lưu ngay trên bộ nhớ của máy (không lưu xuống sheet), khi cần thao tác xử lý cũng cực nhanh. Có những việc bạn phải xử lý ở phía Client (duyệt, xem các Record...), có những việc phải nhờ Server xử lý như: tổng hợp dữ liệu các Table để trả kết quả về cho Client... Không phải lúc nào cũng đưa Server làm hết được.

Về cái form email thì rảnh tôi làm demo cho bạn.

 

[ongke0711]

Đối với 2 gợi ý của bạn (listbox và form nhập email) mình cũng nghĩ đến vấn đề này, nhưng là không biết cách làm nên cứ làm theo cách mình hiểu. (Một số thứ trong file là mình đi cóp nhặt trên diễn đàn này đó). Rất mong được bạn làm mẫu để mình hiểu cách làm

Đây là form mẫu chọn email, còn bạn muốn thêm mới email thì tự thiết kế thêm cái form nhập liệu đơn giản với vài control là được rồi.
Góp ý là màu cam, màu đỏ dễ gây mỏi mắt người dùng, nếu nhìn lâu thì không tốt, bạn nên chọn màu nền khác.

 

[MinhKhai]

Đây là form mẫu chọn email, còn bạn muốn thêm mới email thì tự thiết kế thêm cái form nhập liệu đơn giản với vài control là được rồi.
Góp ý là màu cam, màu đỏ dễ gây mỏi mắt người dùng, nếu nhìn lâu thì không tốt, bạn nên chọn màu nền khác.

Cảm ơn @ongke0711 đã dành thời gian tạo cái form lấy email giúp mình. Đúng là cái form này nó không chỉ dễ dàng nạp email mới mà còn có không gian rất rộng rãi để chứa được nhiều email hơn. Chức năng tìm kiếm chạy rất nhanh và có kết quả theo từng ý tự được gõ. Rất tuyệt vời.
Tuy nhiên bạn có thể giúp mình hoàn thiện thêm 1 vấn đề trên form của bạn: Khi chọn email của các phòng bàn khác nhau thì không được, (thằng sau xóa lựa chọn của thằng trước). Ngoài ra nếu bạn có thể chú thích vào mỗi Sub hoặc từng dòng lệnh để mình hiểu và học hỏi được thì thật là tuyệt.
Một lần nữa cảm ơn bạn !

 

[ongke0711]

Tuy nhiên bạn có thể giúp mình hoàn thiện thêm 1 vấn đề trên form của bạn: Khi chọn email của các phòng bàn khác nhau thì không được, (thằng sau xóa lựa chọn của thằng trước). Ngoài ra nếu bạn có thể chú thích vào mỗi Sub hoặc từng dòng lệnh để mình hiểu và học hỏi được thì thật là tuyệt.

- Về việc chọn mail trên Form thì đi theo hướng: sau khi click chọn các mail của một phòng ban nào đó xong phải bấm nút [Chọn] để lưu xuống Sheet, sau đó chọn tiếp phòng ban khác. Làm như thế cho nó gọn code mà cũng không gây khó khăn lắm cho người dùng. Thêm dòng ghi chú trên Form để người dùng biết cách thao tác đỡ hơn tốn thêm một đống code
- Đã thêm ghi chú chi tiết trong code.
Bạn nên ngâm cứu kỹ lại tầm vực khai báo biến, tầm vực hoạt động của các thủ tục.
Có một qui ước nhỏ cũng không phải là chính thức là dùng từ khoá "Dim" cho khai báo biến ở Form, Procedure level còn "Private" thì dùng ở module-level.