VB Question. Strange printing sheet issues

Status
Not open for further replies.

tonycstech

Rules Violation
Joined
Nov 10, 2017
Messages
2
Hi my name is tony ,i was working on a sheet that allows me to send email directly from it.
I program allot in Autoit (more advanced VB) but here i am lost.

Everything works great but there is one problem.
Once VB saves the file with this code
Code:
'Save curent file to desktop for attachment.
    Application.DisplayAlerts = False
    Dim FilePath As String
    FilePath = Environ("USERPROFILE") & "\Desktop\Form.xls"
    ActiveWorkbook.SaveCopyAs FilePath
    Application.DisplayAlerts = True
Two things happen depending on the computer i am on.
Computer #1 I cannot select ANY printer. Printers are being searched but never found. Known as "Searching printers forever" and "Printer grayed out" issue.
I can still print the page, but even the option to print copies is grayed out, so i have to print one page the time.

Computer #2 I cannot select any OTHER printers then default and there is no preview of the print but i can select the # of copies (giving me indication that on a computer #1 i will not be able to select the printer or number of copies i want)

The difference here is most likely because two computers run different versions of excel, #2 being the latest.

My assumption is: Once file is saved, workbook switches to newly saved file. Or something else weird is happening.

Please help me solve the mystery.

Here is entire code of the Email function.

Code:
Sub send_email()
    'check if user and destination are selcted
    If Range("B9") = "Sender name" Then
        MsgBox ("Please select sender name")
        GoTo GetOut
    ElseIf Range("G7") = "Select Hospital" Then
        MsgBox ("Please select destination")
        GoTo GetOut
    End If
    
    'Save copy to desktop
    Application.DisplayAlerts = False
    Dim FilePath As String
    FilePath = Environ("USERPROFILE") & "\Desktop\Form.xls"
    ActiveWorkbook.SaveCopyAs FilePath
    Application.DisplayAlerts = True
    'End Save file without switching to it
    
    'Start Declare variables
    SMTP_Server = Worksheets("Data").Range("E2") 'Refference from data sheet.
    SMTP_SSL = Worksheets("Data").Range("E3") 'Refference from data sheet.
    SMTP_Port = Worksheets("Data").Range("E4") 'Refference from data sheet.
    SMTP_Login = Worksheets("Data").Range("E5") 'Refference from data sheet.
    SMTP_Password = Worksheets("Data").Range("E6") 'Refference from data sheet.
    SMTP_Timeout = Worksheets("Data").Range("E7") 'Refference from data sheet.
    Email_From = Worksheets("Data").Range("E8") 'Refference from data sheet.
    Email_To = Worksheets("Main").Range("I22") 'Refferenced from Main sheet.
    Email_Subject = Worksheets("Data").Range("E11") 'Refference from data sheet.
    Email_Text = Worksheets("Data").Range("E12") 'Refference from data sheet.
    'End Declare variables
    Dim myMail As CDO.Message
    Set myMail = New CDO.Message
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = SMTP_SSL
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTP_Server
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTP_Port
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = SMTP_Login
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = SMTP_Password
    myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTP_Timeout
    myMail.Configuration.Fields.Update
    With myMail
    .Subject = Email_Subject 'Refference from above declared variable
    .From = Email_From 'Refference from above declared variable
    .To = Email_To 'Refference from above declared variable
    .CC = ""
    .BCC = ""
    .TextBody = Email_Text  'Refference from above declared variable
    .AddAttachment FilePath
    End With
    On Error Resume Next
    myMail.Send
    MsgBox ("Mail has been sent to " & Email_To & vbCrLf & "A copy of this document named Form.xls is saved to your desktop.")
    Set myMail = Nothing
GetOut:
End Sub
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

tonycstech

Rules Violation
Joined
Nov 10, 2017
Messages
2

How the hell was i suppose to know that you mod both forums ?
I thought getting away from a clown who shoves rules down to my throat instead of helping out would help me, but you are everywhere.
Are on you MS forums too ?
What about the penguin ?

While both of you pathetic rule obedient asholes were showing rules down to my throat and telling other people to NOT REPLY, i solved my problem by my self.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,514
Congratulations!!! Your offensive attitude has won you a permanent holiday from this forum.

Account & thread closed.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,130
Members
414,429
Latest member
Bilaal xaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top