3月27

Excel VBAでOath認証はできなくはないけど大変でした＞＜

カテゴリ:: Excel; Twitter

ずらーっとならんだタイムライン。
Excel でTwitterをみれたら面白いかなー。

。。。と思ってチャレンジしてみました。

まずは、このサイトにアクセス。

【ハウツー】Excel VBAでWebサービス - ExcelでTwitterと連携する2 (2 ...

ExcelでTwitterクライアントを作る方法を丁寧に説明しています。
ソースコードものっているので、ぺたくた貼ってさあ、と思ったら

「うごかない！」

＞その他のユーザー定義関数については、名前と処理内容を示すに留めます。

解説していない関数があるんですよー（泣）

しかも超重要なOath認証がナイ＞＜。

そこで探してみました！ありました！

【Access】vbaでhmacが正しく計算できた！！

で、これで作ってみたものの。。。認証されないんです＞＜。

正しく計算できているか。検証サイトを探してみました。

HMAC-SHA-1 Tool/Test

結果、ぜんぜん違うってことがわかりました＞＜。。。
どこで間違っちゃったんだろう。。。

もうだ　め　ぽ　。　。　。

なかばあきらめかけてたところ、ふっと目にはいりましたよ。

【ハウツー】Excel VBAでWebサービス - ExcelでTwitterと連携する完成 ...

＞完成サンプルはsample.lzhの中に収録しています。サンプルとしてご活用ください。

な、なんだってー！

そうしてダウンロードして、２００７だったのでパソコン借りに行って動かしてみたら。。。

いけるじゃないですかー ｷｬｰq(≧∇≦*)(*≧∇≦)pｷｬｰ. ｷｬｰ

ちょっと悔しい気もするのですが、うごいたのでよかったです。

そんでもって、Oath認証のところをよーくみてみたら
最後に「Base64」に変換するところが抜けてた事がわかりました。

これです。

Function EncodeBase64(data() As Byte) As String 'MSXML参照設定が必要

   Dim objXML As MSXML2.DOMDocument
   Dim objNode As MSXML2.IXMLDOMElement

   Set objXML = New MSXML2.DOMDocument
   Set objNode = objXML.createElement("b64")

   objNode.DataType = "bin.base64"
   objNode.nodeTypedValue = data
   EncodeBase64 = objNode.Text

   Set objNode = Nothing
   Set objXML = Nothing

End Function

（sample.lzh より拝借。）

※Oath認証のくわしいことは元のサイトを見てみて下さい。

はーーーーー

ぐったり。

ともかく、Excel VBAで Oath認証をかけるのは、
めっちゃタイヘンだけどできなくはない、という事がわかりました。

3月21

お誕生会の申し込みフォームを作る（Googleスプレッドシート）

カテゴリ:: Google; 作成

本日は、お誕生会の申し込みフォームを作りました。

今回はGoogleスプレッドシートにチャレンジ！

GoogleスプレッドシートはWeb上で使えるExcelみたいなものです。

はなれた所に居る人同士が同時に仕事をすすめるのにとても便利。
メールに添付して送ったり、ファイル管理もさすがGoogle、お手の物です。
もちろんマクロも使えます。

今回は、お申し込みフォームを作成して、入力後「申し込み」ボタンをクリックしたら
申し込んだ方と、管理者の方に入力内容を送るようにしました。

フォームの作り方はネタフルさんのサイトを参考にしました。

Googleスプレッドシートをアンケートや応募

で便利に使う方法

Googleスプレッドシートフォーム作成

動画でみると直感的に分かりやすいと思います。

で、ざくざくフォームを作ったら、マクロの作成です。

Googleドキュメントのフォーム機能からGoogle Apps Scriptを使ってメール送信

今回は、「申し込み」ボタンをクリックすると申し込んだ方に加えて、まとめ役の方にもメールを送信するよう改造しました。

フォームができあがったら、右向き三角のアイコンをクリックして「Run」させます。
予定通りにメールが送れたら、

「トリガー」→「Current script's triggers...」をクリックして
どのタイミングでマクロを実行するかを指定します。

今回は登録したスクリプト名と、
「From spreadsheet」（スプレッドシートから）
「On Form submit」　（「決定」信号が送られたら実行）
を選んでOKします。

これで、申し込みフォームから申し込み、
リアルタイムで状況を確認できるようになりました

いかだでしょうか。
Webから使えてしかも無料。
すっごい世の中になったもんです。

さてさて、今日のコードです。

①下記内容でスプレッドシートを作る

A) タイムスタンプ

B) 氏名

C) 職業/業種

D) 連絡先

E) お祝いメッセージを一言！

F) ３分間一芸に出る？

G) emailアドレス

H) 一言

②スプレッドシートを参照するフォームを作る

③フォームで「OK」ボタンがクリックされたらメールが送られる。

考え方はちょう簡単です♪
みなさまも、ぜひぜひチャレンジしてみてください！

// スプレッドシートの操作
var sh = SpreadsheetApp.getActiveSheet();
var rows = sh.getLastRow();
var cols = sh.getLastColumn();
var rg = sh.getDataRange();
Logger.log("rows="+rows+" cols="+cols);

// 件名、本文
var subject = "☆さとちゃんお誕生会☆";
var body = "ご参加ありがとうございます。\n\n";

// メールアドレス入力カラム名の指定
var MAIL_CLMN_NAME = g.getCell(rows, 7).getValue(); // メールアドレス

// メール送信先(管理者等)
var mail_admin = "xxxxx@gmail.com";
var mail_cc = mail_admin;
var mail_reply = mail_admin;
var mail_to = MAIL_CLMN_NAME;
try{

// メール本文作成と送信先メールアドレス取得
for (var j = 1; j <= cols; j++ ) {
var clmn_name = rg.getCell(1, j).getValue(); // カラム名
var clmn_value = rg.getCell(rows, j).getValue(); // 入力値
body += ( clmn_name + " : " + clmn_value + "\n" );
if ( clmn_name === MAIL_CLMN_NAME ) {
mail_to = clmn_value;
}
}

// メール送信
if ( mail_to ) {
MailApp.sendEmail(mail_to, subject, body, {cc : mail_cc, replyTo : mail_reply});
}else{
MailApp.sendEmail(mail_admin, "error spreadsheet none mailaddress", body);
}
}catch(e){
MailApp.sendEmail(mail_admin, "error spreadsheet func: sendMailByFormSubmiited()", e.message);
}

}

3月19

Excelのリストからメールを一括送信するマクロ作りました。

カテゴリ:: Excel; メール

メールを送るExcelマクロを作りました。

ヨガサークルのみんなにメールを送るのに、
CCに全員書いたら１００人分のメアドがまるわかりだしどうしよう、というご相談でした。

まずは「Excel VBA メール送信」で検索。
こちらのサイトを参考にしました。

Excelの一覧からメールを一括送信 « web for the next

しかも、完成済みのExcelファイルまである。
なんて親切な方。ありがとうございます〜

・・・と思ったら。

job_error_code-2147220973

変なエラーがでて動きません

Commerce Server ダイレクトメーラーのトラブルシューティング方法

job_error_code-2147220977
構成または受信者にメッセージを送信しようとしています。 中には、CDO エラーを job_error_str.
サーバーの応答が: 550... .unable を中継しています.
推奨される解決方法 Commerce Server の IP アドレスへの SMTP 中継を追加します。
job_error_code-2147220973
構成または受信者にメッセージを送信しようとしています。 中には、CDO エラーを job_error_str.トランスポート サーバーへの接続に失敗しました。
推奨される解決方法 SMTP サーバーが利用可能ではありません。SMTP サーバーが電子メール メッセージを送信できることを確認します。「関連情報」に記載の資料を参照してください。
job_error_code-2147220975
CDO エラーを job_error_str を作成または、メッセージを送信しようとしました。メッセージが SMTP サーバーに送信できませんでした。
トランスポート エラー コードは 0x800CCC13 でした。サーバーの応答がないです。
推奨される解決方法 ダイレクト メール エンジニア バックアップするには、SMTP サーバーを引き起こしている可能性がします。可能な場合は、リモートの SMTP サーバーではなくドロップ ディレクトリを使用してテストします。これが不可能な場合は、SMTP をテストするため最小限に開くことを確認するのには、SMTP サーバーの設定を確認します。
サポートページを見ると、パラメータが変なんだっていう。
でも、このパラメータで普通にメールの送受信ができるのに、、、なんでだろう。

そして、謎が解けましたよ！

 VBForums > Visual Basic > Network Programming
 How to send mail using vbsendmail.dll and smtp.gmail.com

（中略）
Quote:
Originally Posted by bachir8k 
Ok guys, this is a new code i am working with now, its more better because even hotmail will put your email in the inbox and not in the junk.

Code:
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

schema = "http://schemas.microsoft.com/cdo/configuration/"
Flds.Item(schema & "sendusing") = 2
Flds.Item(schema & "smtpserver") = "smtp.gmail.com" 
Flds.Item(schema & "smtpserverport") = 465
Flds.Item(schema & "smtpauthenticate") = 1
Flds.Item(schema & "sendusername") = "youremail@gmail.com"
Flds.Item(schema & "sendpassword") =  "yourpassword"
Flds.Item(schema & "smtpusessl") = 1
Flds.Update

With iMsg
	.To = "info@bachir.com"
	.From = "Bachir El Charif <info@bachir.com>"
	.Sender = "Bachir.com"
	.ReplyTo = "info@bachir.com"
	.Subject = "A message from Bachir."
	.HTMLBody = "This is a html email, please ignore"
	.Organization = "Bachir"
	Set .Configuration = iConf
	SendEmailGmail = .Send
End With

set iMsg = nothing
set iConf = nothing
set Flds = nothing
in either way you should be aware that your email will be from youremail@gmail.com even if you have entered a different value for the from parameter.
if you want your client to receive an email from your website email address, you should configure your website and create a google webmail application for your website, but you should have all access on your domain configuration such as DNS Records.

good luck

Flds.Item(schema & "smtpusessl") = 1

そうだ、アウトルックでも「SSL認証を使う」 にしてたっけ。

そこでこの一行をいれてみたら。

ばっちりです☆
メールおくれました


＜今回お世話になったサイト様＞






Windowsで簡単メール送信プログラム｜BTY備忘録

Excelの一覧からメールを一括送信｜ web for the next


 VBForums > Visual Basic > Network Programming
 How to send mail using vbsendmail.dll and smtp.gmail.com


ありがとうございました！ 


Excel一括送信のコードです。


♪♪完成したコードはこちらです♪♪ 




Sub SendMail()

Dim i As Integer
Dim oMsg
 
Set oMsg = CreateObject("CDO.Message")
    
    '’メールサーバーの設定
    oMsg.TextBodyPart.Charset = "ISO-2022-JP"
    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Cells(1, 1)
    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Cells(2, 1)
    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = Cells(3, 1)
    oMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = Cells(4, 1)
    
    '’メール送信設定
    oMsg.From = Cells(6, 1)
    oMsg.Subject = Cells(7, 1)
    oMsg.TextBody = Cells(8, 1)

   '’添付ファイル
    oMsg.AddAttachment (Cells(9, 1) & "\" & Cells(10, 1))

  
    '’すべてのあて先に送る。
    i = 1
    Do
        oMsg.To = Cells(i, 2)
        oMsg.Configuration.Fields.Update
        oMsg.Send
        
　' 送信がおわったらオレンジに色をつける
        Cells(i, 2).Interior.ColorIndex = 36
        i = i + 1
    
        ''３秒停止:メールサーバーのスパム判定時対策
        'Application.Wait [ NOW() + "0:00:03" ]
  
    
    Loop Until Cells(i, 2) = ""

Set oMsg = Nothing

End Sub


以上、シンプルにしあげてみました♪
使う時は、対応するExcelシートを作ってくださいね。

ではでは。