Cannot run new macros

[cosiek]

Hi All,
I have strange problem. I am complete amateur in case of macro in Excel.

Lastly wanted to use some specific code found in Internet, however whenever I am trying to run it (pressing green triangel) execution is hapenning for only 1 macro that is like to be hardcoded to be used all the time.
This macro is all the time used:
1.JPG

What I do?
1. Enter the Macros and create the new macro
2. In empty sheet paste code to be checked
3. Press green triangle and pop up window throws me the macro which is all the time in Excel.
2.JPG

Can you help me with this strange behaviour?

Regards,
Jakub

[rorya]

Your macro requires arguments, so you will not see it listed in the macro dialog. How do you intend to provide the parameters for it?

[cosiek]

Sorry for stupid question, but what d you mean by arguments?
You mean that there is no specification re cells etc?

[rorya]

Everything in the brackets after Mail_Workbook is an argument whose value must be provided for the macro to run.

[cosiek]

You are saying about?
Attachment 700267

I wanted to reuse the the macro @dflak suggested here: https://www.excelforum.com/excel-pro...ml#post5209367 but have the problems.

[rorya]

In that workbook, MailIt is the only macro you can run - it calls the other code, passing the values necessary. You should adapt the MailIt code to pass whatever other values you want (copy the routine and give it another name).

[cosiek]

Sorry but I do not follow what I should do. Can I somehow do not have MailIt as default one? Because this one excludes other macros from creation and execution.

[rorya]

MailIt does not in any way prevent you from creating or running other macros, so I'm afraid I don't know what you mean. When I said it was the only one you could run, I meant it was the only macro in that workbook that can be run from the macros dialog because it doesn't need any information passed to it. The Mail_Workbook routine for example needs you to give it several bits of information (the address to send to, the subject line, the email body and so on) and that is why it does not show up in the macros dialog.

[cosiek]

Thank you, understood.
So parameters need to be provided. Can you help me with that?

This is code provided by user as solution for my case:

Option Explicit 
 
Sub Mail_Workbook(ToString As String, SubjectString As String, BodyString As String, _ 
    Optional CCString As String, Optional BCCString As String, Optional AttachmentName As String) 
    Dim OutApp As Object 
    Dim OutMail As Object 
 
    Set OutApp = CreateObject("Outlook.Application") 
    Set OutMail = OutApp.CreateItem(0) 
 
    On Error Resume Next 
   ' Change the mail address and subject in the macro before you run it. 
    With OutMail 
        .To = ToString 
        If CCString <> "" Then 
            .CC = CCString 
        End If 
        If BCCString <> "" Then 
            .BCC = BCCString 
        End If 
        .Subject = SubjectString 
        .Body = BodyString 
        If AttachmentName <> "" Then 
            .Attachments.Add (AttachmentName) 
        End If 
         
        '.Send 
        .display 
    End With 
    On Error GoTo 0 
 
    Set OutMail = Nothing 
    Set OutApp = Nothing 
End Sub 
 
Function RangetoHTML(rng As Range) 
Dim fso As Object 
Dim ts As Object 
Dim TempFile As String 
Dim TempWB As Workbook 
 
TempFile = ThisWorkbook.Path & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
 
'Copy the range and create a new workbook to past the data in 
rng.Copy 
Set TempWB = Workbooks.Add(1) 
With TempWB.Sheets(1) 
    .Cells(1).PasteSpecial Paste:=8 
    .Cells(1).PasteSpecial xlPasteValues, , False, False 
    .Cells(1).PasteSpecial xlPasteFormats, , False, False 
    .Cells(1).Select 
    Application.CutCopyMode = False 
    On Error Resume Next 
    .DrawingObjects.Visible = True 
    .DrawingObjects.Delete 
    On Error GoTo 0 
End With 
 
'Publish the sheet to a htm file 
With TempWB.PublishObjects.Add( _ 
    SourceType:=xlSourceRange, _ 
    Filename:=TempFile, _ 
    Sheet:=TempWB.Sheets(1).Name, _ 
    Source:=TempWB.Sheets(1).UsedRange.Address, _ 
    HtmlType:=xlHtmlStatic) 
    .Publish (True) 
End With 
 
'Read all data from the htm file into RangetoHTML 
Set fso = CreateObject("Scripting.FileSystemObject") 
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
RangetoHTML = ts.readall 
ts.Close 
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=") 
 
'Close TempWB 
TempWB.Close savechanges:=False 
 
'Delete the htm file we used in this function 
Kill TempFile 
 
Set ts = Nothing 
Set fso = Nothing 
Set TempWB = Nothing 
End Function  

It has to pick up in each row:
1. the email addresses from column F
2. take the content from column J
3. take the subject from M1 (all the time the same)
4. Open outlook, paste details in seperate mails and send.

I have created my version but still do not understood by Excel:

Option Explicit 
 
Sub Mail_Workbook(ToString As String, SubjectString As String, BodyString As String, _ 
    Optional CCString As String, Optional BCCString As String, Optional AttachmentName As String) 
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim clE As Range 
    Dim shtA As Worksheet 
 
    Set OutApp = CreateObject("Outlook.Application") 
    Set OutMail = OutApp.CreateItem(0) 
   Set shtA = Sheets("Arkusz1") 
   SubjectString = Range("Mail_Subject") 
    
   For Each clE In Range("Table1[mail]") 
    ToString = clE.Value 
    BodyString = shtA.Cells(clE.Row, "J") 
 
    On Error Resume Next 
   ' Change the mail address and subject in the macro before you run it. 
    With OutMail 
        .To = ToString 
        If CCString <> "" Then 
            .CC = CCString 
        End If 
        If BCCString <> "" Then 
            .BCC = BCCString 
        End If 
        .Subject = SubjectString 
        .Body = BodyString 
        If AttachmentName <> "" Then 
            .Attachments.Add (AttachmentName) 
        End If 
         
        '.Send 
        .display 
    End With 
    On Error GoTo 0 
 
    Set OutMail = Nothing 
    Set OutApp = Nothing 
End Sub 
 
Sub RangetoHTML(rng As Range) 
Dim fso As Object 
Dim ts As Object 
Dim TempFile As String 
Dim TempWB As Workbook 
 
TempFile = ThisWorkbook.Path & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
 
'Copy the range and create a new workbook to past the data in 
rng.Copy 
Set TempWB = Workbooks.Add(1) 
With TempWB.Sheets(1) 
    .Cells(1).PasteSpecial Paste:=8 
    .Cells(1).PasteSpecial xlPasteValues, , False, False 
    .Cells(1).PasteSpecial xlPasteFormats, , False, False 
    .Cells(1).Select 
    Application.CutCopyMode = False 
    On Error Resume Next 
    .DrawingObjects.Visible = True 
    .DrawingObjects.Delete 
    On Error GoTo 0 
End With 
 
'Publish the sheet to a htm file 
With TempWB.PublishObjects.Add( _ 
    SourceType:=xlSourceRange, _ 
    Filename:=TempFile, _ 
    Sheet:=TempWB.Sheets(1).Name, _ 
    Source:=TempWB.Sheets(1).UsedRange.Address, _ 
    HtmlType:=xlHtmlStatic) 
    .Publish (True) 
End With 
 
'Read all data from the htm file into RangetoHTML 
Set fso = CreateObject("Scripting.FileSystemObject") 
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
RangetoHTML = ts.readall 
ts.Close 
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=") 
 
'Close TempWB 
TempWB.Close savechanges:=False 
 
'Delete the htm file we used in this function 
Kill TempFile 
 
Set ts = Nothing 
Set fso = Nothing 
Set TempWB = Nothing 
End Sub  

Do you have and idea, what is mising or how it should be corrected?
Thank yu very much for your kind help.
Regards,
Jakub

[rorya]

You need a separate macro that calls the first bit of code and passes the necessary arguments, just as you had before:

Please Login or Register  to view this content.

[cosiek]

Thank you very much. It worked!
Do you know how to add in the code the definition for sending e-mail account?
I found such thing:

.SendUsingAccount ...

[rorya]

You need to add that in the Mail_Workbook procedure, inside the With...End With block.

[cosiek]

Thank you. I did as you mentioned.
Seems working, however now I got Type mismatch on mail address. I gave correct name for account, which is defined in my Outlook app.
Attachment 700315

Below code I use. Please let me know if you know what may the reason.

Option Explicit 
 
Sub Mail_Workbook(ToString As String, SubjectString As String, BodyString As String, _ 
    Optional CCString As String, Optional BCCString As String, Optional AttachmentName As String) 
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim oAccount As Outlook.Account 
 
    Set OutApp = CreateObject("Outlook.Application") 
    Set OutMail = OutApp.CreateItem(0) 
Set oAccount = "[email protected]" 
    On Error Resume Next 
   ' Change the mail address and subject in the macro before you run it. 
    With OutMail 
    .SendUsingAccount = oAccount 
        .To = ToString 
        If CCString <> "" Then 
            .CC = CCString 
        End If 
        If BCCString <> "" Then 
            .BCC = BCCString 
        End If 
        .Subject = SubjectString 
        .Body = BodyString 
        If AttachmentName <> "" Then 
            .Attachments.Add (AttachmentName) 
        End If 
        '.Send 
        .display 
    End With 
    On Error GoTo 0 
 
    Set OutMail = Nothing 
    Set OutApp = Nothing 
End Sub 
 
Function RangetoHTML(rng As Range) 
Dim fso As Object 
Dim ts As Object 
Dim TempFile As String 
Dim TempWB As Workbook 
 
TempFile = ThisWorkbook.Path & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
 
'Copy the range and create a new workbook to past the data in 
rng.Copy 
Set TempWB = Workbooks.Add(1) 
With TempWB.Sheets(1) 
    .Cells(1).PasteSpecial Paste:=8 
    .Cells(1).PasteSpecial xlPasteValues, , False, False 
    .Cells(1).PasteSpecial xlPasteFormats, , False, False 
    .Cells(1).Select 
    Application.CutCopyMode = False 
    On Error Resume Next 
    .DrawingObjects.Visible = True 
    .DrawingObjects.Delete 
    On Error GoTo 0 
End With 
 
'Publish the sheet to a htm file 
With TempWB.PublishObjects.Add( _ 
    SourceType:=xlSourceRange, _ 
    Filename:=TempFile, _ 
    Sheet:=TempWB.Sheets(1).Name, _ 
    Source:=TempWB.Sheets(1).UsedRange.Address, _ 
    HtmlType:=xlHtmlStatic) 
    .Publish (True) 
End With 
 
'Read all data from the htm file into RangetoHTML 
Set fso = CreateObject("Scripting.FileSystemObject") 
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
RangetoHTML = ts.readall 
ts.Close 
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=") 
 
'Close TempWB 
TempWB.Close savechanges:=False 
 
'Delete the htm file we used in this function 
Kill TempFile 
 
Set ts = Nothing 
Set fso = Nothing 
Set TempWB = Nothing 
End Function 
 
Option Explicit 
 
Sub SendNewMails() 
 
    Dim clE As Range 
    Dim shtA As Worksheet 
 
   Set shtA = Sheets("Arkusz1") 
Dim SubjectString As String 
   SubjectString = Range("Mail_Subject") 
    
   For Each clE In Range("Table1[mail]") 
    Dim ToString As String 
    ToString = clE.Value 
    Dim BodyString As String 
    BodyString = shtA.Cells(clE.Row, "J") 
 Mail_Workbook ToString, SubjectString, BodyString 
 Next 
End Sub  

Regards,
Jakub

[rorya]

You're passing an address, which is text, so you should declare the variable as String, and don't use Set when assigning the address to the variable.

[cosiek]

Thank you.
Now I do not get any error, however the thrown mail message is having the default email account used in the Outlook. Not the desired one.
Is there any way to push it with the code?

[cosiek]

I figured it out.
Using .SentOnBehalfOfName helped.

Thank you for all help.