UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Excel Vba Refresh From Queries Save And Email    
 
   
cheekybuddha
post May 15 2020, 09:14 AM
Post#21


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


Apologies - I was scanning too quickly to try and spot an error.

Actually, it *should* have been right as it was before. blush.gif

So, if you want, just graft in the 'ugly' code (it's not ugly, I just like typing less!!) just to make sure you can get it working.

--------------------


Regards,

David Marten
Go to the top of the page
 
techexpressinc
post May 15 2020, 10:18 AM
Post#22



Posts: 495
Joined: 6-October 08
From: indiana, usa


Getting this strange error that locks up the Excel.
Russ
This post has been edited by techexpressinc: May 15 2020, 10:18 AM
Attached File(s)
Attached File  ERROR_5_15_2020_1040am.jpg ( 27.67K )Number of downloads: 0
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
cheekybuddha
post May 15 2020, 10:27 AM
Post#23


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


Please post the code as you have it now, that is causing the lockup.

--------------------


Regards,

David Marten
Go to the top of the page
 
techexpressinc
post May 15 2020, 10:50 AM
Post#24



Posts: 495
Joined: 6-October 08
From: indiana, usa


Strange one time works, next that bad error with lockup - appears to be in the Email part.
CODE
Sub Auto_Open()
Application.DisplayAlerts = False
  Dim dd As Date
  Dim FormattedDate     As String
  Dim DateFileName      As String
  Dim strSubject        As String, _
      strFrom           As String, _
      strTo             As String, _
      strCC             As String, _
      strBCC            As String
  Const SAVE_PATH       As String = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxs\businesslogistics\autoreports\"
  Const cdo_config_path As String = "http://schemas.microsoft.com/CDO/configuration/"
  Const EXT_XLSX        As String = ".xlsx"
  Const EXT_XLSM        As String = ".xlsm"
  dd = Now()
  With ThisWorkbook
    .Worksheets("RefreshedTimeStamp").Range("A3").Value = Format(dd, "mm/dd/yyyy hh:mm:ss")
    .RefreshAll
    FormattedDate = Format(dd, "mm\-dd\-yyyy\-hh\-nn")
    DateFileName = "Refreshed" & FormattedDate & "_XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxound"
    .Save
    .SaveCopyAs Filename:=SAVE_PATH & DateFileName & EXT_XLSM
    With Application.Workbooks.Open(SAVE_PATH & DateFileName & EXT_XLSM)
      .SaveAs Filename:=SAVE_PATH & DateFileName & EXT_XLSX, _
              FileFormat:=xlOpenXMLWorkbook, _
              Password:="", _
              WriteResPassword:="", _
              ReadOnlyRecommended:=False, _
              CreateBackup:=False
      .Close
    End With
  End With
'  EMAIL THE EXCEL '
'  EMAIL THE EXCEL '
Dim TxtAttachments As String
Dim STRFILENAMEAll As String
STRFILENAMEAll = SAVE_PATH & DateFileName & EXT_XLSX
strCC = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxm"
Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxated PLAN-IDS."
    objMessage.from = "PCPDComxm"
    objMessage.To = "PCPDCommunxm"
    objMessage.TextBody = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxreported. "
    objMessage.cc = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx"
    objMessage.bcc = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/CDO/configuration/sendusing") = XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx
objMessage.Configuration.Fields.Update
TxtAttachments = STRFILENAMEAll
   objMessage.bcc = strBCC
   objMessage.cc = strCC
   objMessage.AddAttachment STRFILENAMEAll
   objMessage.Send
End Sub

This post has been edited by techexpressinc: May 15 2020, 10:50 AM

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
cheekybuddha
post May 15 2020, 11:19 AM
Post#25


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


Are you saying that one email does get sent? Or does it error before that?

Did you add Option Explicit to the top of your code module? (or check that it is there already?)

You haven't declared the object variable objMessage. Add the line:
CODE
' ...
Dim TxtAttachments As String
Dim STRFILENAMEAll As String
Dim objMessage As Object
' ...


I suspect there might be a timing issue as well.

But let's try and look at this in small chunks first. Make the above changes and if you still get the crash, then we'll see what else is possible.

--------------------


Regards,

David Marten
Go to the top of the page
 
techexpressinc
post May 15 2020, 11:26 AM
Post#26



Posts: 495
Joined: 6-October 08
From: indiana, usa


It is working now, I will add those lines I want it stable an run daily.
Will post final product.
Russ



--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
cheekybuddha
post May 15 2020, 11:52 AM
Post#27


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


>> It is working now <<
yayhandclap.gif

>> Will post final product. <<
Yes, please do!

thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
techexpressinc
post May 15 2020, 12:51 PM
Post#28



Posts: 495
Joined: 6-October 08
From: indiana, usa


Thanks again you are always an Excellent Helper. notworthy.gif
Here is the code, now back to my assignment projects, i did this for kicks wink.gif
CODE
Sub Auto_Open()
Application.DisplayAlerts = False
  Dim dd As Date
  Dim FormattedDate     As String
  Dim DateFileName      As String
  Dim strSubject        As String, _
      strFrom           As String, _
      strTo             As String, _
      strCC             As String, _
      strBCC            As String
  Const SAVE_PATH       As String = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxesslogistics\autoreports\"
  Const cdo_config_path As String = "http://schemas.microsoft.com/CDO/configuration/"
  Const EXT_XLSX        As String = ".xlsx"
  Const EXT_XLSM        As String = ".xlsm"
  dd = Now()
  With ThisWorkbook
    .Worksheets("RefreshedTimeStamp").Range("A3").Value = Format(dd, "mm/dd/yyyy hh:mm:ss")
    .RefreshAll
    FormattedDate = Format(dd, "mm\-dd\-yyyy\-hh\-nn")
    DateFileName = "Refreshed_" & FormattedDate & "__2019_Plan_Compliance_Team_Turnaround"
    .Save
    .SaveCopyAs Filename:=SAVE_PATH & DateFileName & EXT_XLSM
    With Application.Workbooks.Open(SAVE_PATH & DateFileName & EXT_XLSM)
      .SaveAs Filename:=SAVE_PATH & DateFileName & EXT_XLSX, _
              FileFormat:=xlOpenXMLWorkbook, _
              Password:="", _
              WriteResPassword:="", _
              ReadOnlyRecommended:=False, _
              CreateBackup:=False
      .Close
    End With
  End With
'  EMAIL THE EXCEL '
'  EMAIL THE EXCEL '
Dim TxtAttachments As String
Dim STRFILENAMEall As String
Dim objMessage As Object
STRFILENAMEall = SAVE_PATH & DateFileName & EXT_XLSX
strCC = "RusXXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxcom"
Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxeam_Turnaround " & "-Refreshed=" & FormattedDate
    objMessage.from = "PCPDCommuXXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxcom"
    objMessage.To = "Sonia.Murphy@lfg.com"
    objMessage.TextBody = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxnaround-V-03-11-2020 - any issues email PCPXXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxon mailbox. "
    objMessage.cc = "PCPDCommuXXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxom"
    objMessage.bcc = ""
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/CDO/configuration/sendusing") = XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxxm"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = XXXXXXXXXXXXxxxxxxxxxxxxxxxxxxxxx
objMessage.Configuration.Fields.Update
TxtAttachments = STRFILENAMEall
   objMessage.bcc = strBCC
   objMessage.cc = strCC
   objMessage.AddAttachment STRFILENAMEall
   objMessage.Send
Kill STRFILENAMEall
STRFILENAMEall = SAVE_PATH & DateFileName & EXT_XLSM
Kill STRFILENAMEall
Application.Quit
End Sub

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
cheekybuddha
post May 15 2020, 01:36 PM
Post#29


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


Glad we got there in the end!

Keep up the good work! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    8th July 2020 - 12:41 AM